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:

Vlookup to the left

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:

Vlookup left data

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:

Vlookup left array table

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:

Vlookup to the 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.

One thought on “How to do a Vlookup to the left in Google Sheets?”

  1. Thank you so much. I was looking for a way to do this. I’m not sure why I didn’t think of just creating a temporary array.
    I’ve used index & match before but they do not work well when they are nested in the arrayformula() function. This works perfectly though.

Leave a Reply

Your email address will not be published. Required fields are marked *