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.

15 thoughts 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.

  2. I can’t use {} formula because it makes two column staked on one column. How to solve it?
    Thank you for your help ๐Ÿ™‚

    1. Hey Marcell,

      Try using a backslash instead of a comma and that should do the trick to give you the two columns side by side:

      ={B1:B10\A1:A10}

      (I’m presuming you’re based in one of the European countries that use commas as decimal operators.)

      Cheers,
      Ben

  3. Very Nice – helped me complete an update to a current function.
    …here is my working snippet which shows looking up in another table:

    =IFERROR(VLOOKUP(VLOOKUP(A2,PlayerNameMapping!A:D,2,false),{RGdownloadProj!$E$1:$E$1000,RGdownloadProj!$A$1:$A$1000},2,false), “”)

  4. Hi,

    I have been trying to do a 3 column lookup, i have a range of numbers 1-10 in Column A, Columb B contains a classification New, Like new, returned, used, na, etc and column C applied value either Y or N, i want to report back the cell in column A that matches B and C columns but it only reports the info in the match for column B but not Column C =INDEX(B:B,{MATCH(E2,C:C,0),MATCH(“N”,D:D,0)})

    I want to search on Colum B where it has value “used” heled in cell E2 and the value C is equal to “N”

    Not sure what i am missing or doing wrong

  5. So, my left look up is from sheet to C back to B. I keep trying ($cS3, p1,C:B,2,FALSE) so what your saying is it should work to add vlookup ($c$3,p1{C:B}2,false) ?? Help because I keep getting errors and REF..

Leave a Reply

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