With this setup, VLOOKUP finds the option associated with a cost of 3000, and returns “C”. Note: this is a more advanced topic. If you are just getting started with VLOOKUP, start here.
Introduction
A key limitation of VLOOKUP is it can only lookup values to the right. In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP. As a result, with standard configuration, there is no way to use VLOOKUP to “look left” and reverse the original lookup. From the standpoint of VLOOKUP, we can visualize the problem like this:
The workaround explained below uses the CHOOSE function to rearrange the table inside VLOOKUP. Using G5 as the lookup value (“C”), and the data in B5:D8 as the table array, VLOOKUP performs a lookup on values in column B, and returns the corresponding value from column 3 (column D), 3000. Notice zero (0) is provided as the last argument to force an exact match. The formula in G10 simply pulls the result from H5: To perform a reverse lookup, the formula in H10 is: The tricky bit is the CHOOSE function, which is used to rearrange the table array so that Cost is the first column, and Option is the last: The CHOOSE function is designed to select a value based on a numeric index. In this case, we are supplying three index values in an array constant: In other words, we are asking for column 3, then column 2, then column 1. This is followed by the three ranges that represent each column of the table in the order they appear on the worksheet. With this configuration, CHOOSE returns all three columns in a single 2D array like this: If we visualize this array as a table on the worksheet, we have:
Note: the headings are not part of the array and are shown here for clarity only. Effectively, we have swapped columns 1 and 3. The reorganized table is returned directly to VLOOKUP, which matches 3000, and returns the corresponding value from column 3, “C”.
With INDEX and MATCH
The above solution works fine, but it is hard to recommend since most users will not understand how the formula works. A better solution is INDEX and MATCH, using a formula like this: Here, the MATCH function finds the value 3000 in D5:D8, and returns its position, 3: Note: MATCH is configured for an exact match by setting the last argument to zero (0). MATCH returns a result directly to INDEX as the row number, so the formula becomes: and INDEX returns the value from the third row of B5:B8, “C”. This formula shows how INDEX and MATCH can be more flexible than VLOOKUP.
With XLOOKUP
XLOOKUP also provides a very good solution. The equivalent formula is: With a lookup value from G10 (3000), a lookup array of D5:D8 (costs) and a results array of B5:B8 (options), XLOOKUP locates the 3000 in lookup array, and returns the corresponding item from the results array, “C”. Because XLOOKUP performs an exact match by default, there is no need to set the match mode explicitly.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
