How to properly pack a parachute?
Benefit. Edition 2, revised.
Let’s say we have the following table of orders:
We need to know, for example, what was the amount of Ivanov’s third order or when Petrov executed his second deal. The built-in VLOOKUP function can only search for the first occurrence of the last name in the table and will not help us. Questions like “Who was the manager of order number 10256?” will also remain unanswered, tk. the built-in VLOOKUP is not able to return values from columns to the left of the search one.
Both of these problems are solved in one fell swoop – let’s write our own function that will look not only for the first, but, in the general case, for the Nth occurrence. Moreover, it will be able to search and produce results in any columns. Let’s call it, say, VLOOKUP2.
Open the Visual Basic Editor by pressing ALT+F11 or by selecting from the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor), insert a new module (menu Insert – Module) and copy the text of this function there:
Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _ N As Long, ResultColumnNum As Long) Dim i As Long, iCount As Long Select Case TypeName(Table) Case "Range" 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 Case "Variant()" For i = 1 To UBound(Table) If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1 If iCount = N Then VLOOKUP2 = Table(i, ResultColumnNum) Exit For End If Next i End Select End Function
Close the Visual Basic Editor and return to Excel.
Now through Insert – Function (Insert — Function) in category User Defined (User Defined) you can find our VLOOKUP2 function and use it. The function syntax is as follows:
=VLOOKUP2(table; number_of_column_where_we look for; lookup_value; N; number_of_column_from_to_get_value)
Now the limitations of the standard function are not a hindrance to us:
PS Special thanks to The_Prist for improving the function so that it can search in closed books.
- Finding and substituting data from one table to another using the VLOOKUP function
- “Left VLOOKUP” using the INDEX and MATCH functions