Lookup functions are widely used in Excel models. However, one of the most popular lookup functions, VLOOKUP, can only search the left-most column in the search area. This function will fall short if you need to search in the middle of a table and return values from its left. In other words, if you want to search for a value in a range under column D, and return the matching value from column B, you will have to use a different method. This behavior is called a reverse lookup. In this guide, we are going to show you how to perform an Excel reverse lookup using the CHOOSE function.
Returning an array using the CHOOSE function
The CHOOSE function can return an array, and we can swap the order of the columns afterwards. Let's take the same example above: The lookup range is under the column D, and the return range is under the column B. Using the CHOOSE function, we can tell the VLOOKUP that column D is the first (left-most) column and column B is the second column in an array.
The CHOOSE function can return an array if you enter an array or a reference as one of its value arguments. On the other hand, we need to return every value arguments of the outcome. To do this, we need to provide an array of index numbers, instead of a single value. For example; {1,2} makes the function return an array of value1 and value2 combined.
The following formula combines the arrays in D3:D16 and B3:B16 arguments, and returns an array incorporating both.
Now we are able to produce an array that fit our needs, the next step is to use the array in a lookup formula to perform reverse lookup.
VLOOKUP and CHOOSE
You can use either the VLOOKUP and HLOOKUP functions in this method. In our example, we're going to be using VLOOKUP due to the fact that our ranges are vertical - e.g. D3:D13 and B3:B16.
Use the formula with CHOOSE as table_array of the VLOOKUP function. Since the array returned by the CHOOSE function only contains two columns, you will need to set 2 for col_index_num argument. This means that the column number between the two target columns are not relevant when performing a reverse lookup.
Let's establish a formula that can search for "Blastoise" in a range under column D, and and return the matching value under column B:
HLOOKUP
If you want to use the CHOOSE function to provide an array for a HLOOKUP function, the two steps below are important:
- Combine the horizontal ranges, e.g. K4:Q4 and K2:Q2
- Provide a vertical array for the index_num argument, e.g. {1;2}
Regarding the second item, you need to use a semicolon (;) to separate 1 and 2 instead of a comma (,). While a comma indicates columns, a semicolon indicates rows.
Alternatives
You can also use the INDEX & MATCH combination, or the LOOKUP function to do an Excel reverse lookup. Please note that the LOOKUP function performs an approximate search, and assumes that your lookup array is sorted in ascending order. You can set the search for type by providing a [range_lookup] argument in VLOOKUP and HLOOKUP functions. You can also specify a search type when using MATCH function. As a result, the INDEX & MATCH combination is more viable alternative.
