Add-in for Reverse Lookup in Excel
Any user who has used the VLOOKUP formula for a while starts to feel the frustration arising from the fact that VLOOKUP can only lookup to the right of the column in which the Lookup value is kept. MATCH and INDEX functions can be used to get around this but the entire formula is much longer and harder to remember compared to the simpler VLOOKUP. I always wonder why the reverse lookup facility is not provided as a standard feature in Excel.
I have been facing this frustration a lot this year, and to get around it, I wrote a custom user function 'rLOOKUP' that works like VLOOKUP but also allows reverse lookups. The function simply uses the INDEX and MATCH techniques in VBA. It is provided in an Excel add-in for ease of use.
Similar function can be written for HLOOKUPs but I have provided VLOOKUP here due to its extensive use compared to HLOOKUP.
Download the add-in here: .xlam file (15 KB).
Save the file on the computer and install it into Excel by choosing File -> Options ->Add-ins. Click on the "Go..." button and then choose Browse. Select the saved file and click the OK button to complete the installation. Once installed, "Reverse_Lookup" appears in the list of available Add-ins and the rLOOKUP function becomes available in all workbooks.
Detailed page on adding/removing add-ins into Excel can be obtained at this MS Office support page.
The arguments for the rLOOKUP function are the same as that of the VLOOKUP function, see screenshots below. To perform a reverse lookup, simply enter a negative number into the argument 'Col_index_num' (see examples further below). The formula will then lookup towards the left. It is as simple as that!
One difference between the 2 functions is that if Range_lookup is omitted in VLOOKUP, the result will not be exact. However, the opposite is true for rLOOKUP, by default, this function will return the exact value. Again, this is because most of the time, people want the exact value from lookups. The error values returned by the rLOOKUP function are similar to those returned by VLOOKUP.
The following Excel data shows office locations, employee names and designations.
To answer the query, "who is in office B43", we can use the VLOOKUP formula:
VLOOKUP("B43",A1:C6,2,FALSE) which will return "Dilbert".
However, if we want to know "Where is Dilbert's office?" then VLOOKUP will not work. This is where the rLOOKUP formula comes in: we can use the rLOOKUP formula:
The formula will look for the entry "Dilbert" in column B and return "B43" from column A as the result. Note the negative 2 in the formula, this makes the function look at the adjacent column on the left.
The formula works both ways, so
rLOOKUP("B43",A1:C6,2) returns "Dilbert" and
rLOOKUP("Dilbert",A1:B6,-2) returns "B43".
More examples below:
The file containing these examples can be downloaded here.. Note that the add-in must be installed first, otherwise it will return #NAME? error.
How it works
The formula uses the INDEX and MATCH functions to run the reverse lookup. There are numerous explanations of the use of the use of these functions for reverse lookups, one good source is this page on Tom's Tutorials.
The source code for the function is simple, see below.
Function rLOOKUP(Lookup_value, Table_array As Range,
Col_index_num As Integer, Optional Range_lookup As Boolean)
'Custom function that allows forward and reverse vertical lookups
'Works like VLookup for positive Col_index_num, and does reverse lookups for negative Col_index_num
'Unlike VLOOKUP, it looks for the exact value by default
'Nitin Mehta, www.Engineers-Excel.com
Dim Source_Col As Range, Dest_Col_num As Integer
'The Source Column is the Column where the Lookup_value is located. It is the leftmost column if
'Col_index_number is positive. If Col_index_number is negative, it is the rightmost column
Set Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))
'Dest_col_num is the column number within the Table_array range from where we have to pick up the entry against the Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)
'Use MATCH and INDEX functions for the Lookup.
'Use of CVErr(xlErrRef) suggested by Brian Canes
rLOOKUP = IIf(Dest_Col_num < 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))