How To VLOOKUP To The Left In Google Sheets?

The VLOOKUP function 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 VLOOKUP to the left?

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 the VLOOKUP to the left 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 literal 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. See Day 10 of my free Advanced Formulas 30-day Challenge course.

See also how to have VLOOKUP return multiple columns.

6 thoughts on “How To VLOOKUP To The Left In Google Sheets?”

  1. All-matches, un-ordered lookup:
    =FILTER(A:A, B:B = $E$2)

    First-match, un-ordered lookup:
    =INDEX(A:A, MATCH($E$2, B:B, 0))

    First-match, ordered lookup: (fastest by n^2 rows)
    =INDEX(A:A, MATCH($E$2, B:B, 1))

  2. Will it work if the range A1:A10 Contains drop down list? If not why?
    # Thanks in advance

  5. Hi, the formula ={B8:B17,A8:A17} doesn’t work in my Google Sheets. Instead of returning a table of 2 columns, mine returns a table with 1 column: column B and below column A. Any idea about how to solve the issue?

Comments are closed.