TOP
# Improved function VLOOKUP (VLOOKUP2)

## Description

## An example of a problem

## VBA code for function VLOOKUP2

### Related Articles:

The built-in function VLOOKUP is one of the most powerful functions in Excel. But it has one significant drawback - it finds only the first occurrence of the desired value in the table and only in the far right column. But if you need the 2nd, 3rd and not the last one?

Let's say we have a table of processed loans like this:

We need to know, for example, what was the amount of the third loan issued to Mike or when John executed his second agreement. Built-in function VLOOKUP knows how to search only the first occurrence of a name in the table and will not help us.

Let's write our function, which will search not only the first, but also any subsequent (Nth) entry. Let's call it, for example, VLOOKUP2.

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

Function VLOOKUP2(Table As Range, _ SearchColumnNum As Integer, _ SearchValue As Variant, _ N As Integer, _ ResultColumnNum As Integer) Dim i As Integer Dim iCount As Integer For i = 1 To Table.Rows.Count If Table.Cells(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1 End If If iCount = N Then VLOOKUP2 = Table.Cells(i, ResultColumnNum) Exit For End If Next i End Function

Close the Visual Basic editor and return to Excel.

Now in the Function Wizard, in the User Defined category, you can find and use our VLOOKUP2 function. The syntax of the function is as follows:

=VLOOKUP2(table ; column_number_where_we_search ; searched_value; entry_number ; column_number_from_which_we_take_the_value )

That is, in order to find the amount of the third loan issued to Mike, you will need to enter:

=VLOOKUP2(A2:A19 ; 1 ; "Mike"; 3 ; 4 )

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Work 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
- YouLibreCalc extension for LO Calc