TOP
# VLOOKUP for combination according to two conditions (VLOOKUP3)

## Description

## An example of a problem

## VBA code for function VLOOKUP3

## Using the function

### Related Articles:

Let's consider an improved version of the function VLOOKUP - VLOOKUP3, which gives us the opportunity to substitute values when two conditions match. This function can be useful in cases where we do not have unique values in the field on which we need to perform a combination.

Suppose we need to join two tables, but we have no unique values:

As you can see, we have borrowers with the same surnames and loan agreements with the same numbers. Combine with a normal function VLOOKUP will be problematic because it matches only one condition at a time and only the first value found.

Let's redo what we're used to VLOOKUP for substituting values according to two conditions.

Open the menu Service - Macro - Editor Visual Basic , insert the new module (menu Insert - Module ) and copy the text of this one there functions:

Function VLOOKUP3(Table1 As Range, _ SearchValue1 As Variant, _ Table2 As Range, _ SearchValue2 As Variant,_ ResultColumn As Range) 'moonexcel.com.ua Dim i As Integer For i = 1 To Table1.Rows.Count If Table1.Cells(i, 1) = SearchValue1 Then If Table2.Cells(i, 1) = SearchValue2 Then VLOOKUP3 = ResultColumn.Cells(i, 1) Exit For End If End If Next i End Function

Close the Visual Basic editor and return to Excel.

Now in Function Wizards in the category User defined you can find our VLOOKUP3 function and use it. The syntax of the function is as follows:

=VLOOKUP3(range1 ; searched_value1 ; range2 ; searched_value2 ; the range from which we substitute values )

That is, in order to correctly substitute the loan amount by last name and agreement number, you will need to enter the following in cell E15:

=VLOOKUP3($A$2:$A$11 ; A15 ; $B$2:$B$11 ; B15 ; $C$2:$C$11 )

Also, do not forget to fix the ranges with a dollar sign ($), so that we do not miss the ranges when copying the formula (for quick fixing, you can also use the F4 key).

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"