VLOOKUP is a super popular formula but suffers from a major drawback. You can’t lookup data to the left!
However, there’s a sneaky trick that lets us VLOOKUP to the left, so we can search for a term and return a result from a column to the left of the original search column:
Table of Contents
How do we create a leftwards VLOOKUP?
What we do is create a new virtual table with an array, where the columns are switched, so the VLOOKUP can work on this temporary, virtual table.
What’s the formula?
Let’s assume we have data in columns A and B. I want to search column B and return the value from column A, then we use:
=VLOOKUP(D2,{$B$1:$B$10,$A$1:$A$10},2,FALSE)
Can I see an example worksheet?
How does this formula work?
Imagine this is your raw data table and you want to search for a name and return the rent figure:
The first step is to use an array formula to create a new table on the fly, and then perform the VLOOKUP on this new temporary table.
This formula creates the temporary table:={B1:B10,A1:A10}
as shown in the following image:
This formula has curly brackets to denote an array and inside, the two columns are swapped. This creates an array output with the two columns reversed.
Note, they don’t have to be adjacent columns, they just happen to be in this simple example.
Next, this formula performs a vlookup on the new temporary table (which is all done inside the vlookup, so you won’t actually see the temporary table):
=VLOOKUP(D2,{$B$1:$B$10,$A$1:$A$10},2,FALSE)
which effectively performs a search like so:
Bingo!
It returns $2,214 as we want.
Note: A better way to solve this is by using a combination of the INDEX and MATCH functions.
