How to do a Vlookup to the left in Google Sheets?

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:

How to do a Vlookup to the left in Google Sheets? 1

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?

Yes, here you go.

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:

reverse vlookup to left

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:

reverse vlookup to left

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:

reverse vlookup to left

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.

Your Can Master Complete Excel in Less Than 2 Coffees $. Most Recommended Course

Leave a Comment