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 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)

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.

27 thoughts on “How to do a Vlookup to the left in Google Sheets?”

1. Roman Seltenreich says:

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. Marcell says:

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

1. Ben says:

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

1. Matthias says:

wow, thanks! Helps me a lot! 🙂

2. Michal says:

Thank you, this is so great!

3. ismail tunalı says:

Thats work, thanks!

3. Slava B says:

Bravo! Quick and simple explanation.

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

5. Michael says:

Hi, Ben! Love your blog!

Is there a way to combine this with IMPORTRANGE and get data off a different spreadsheet?

1. Chris says:

Would love to see this too

2. Wilson says:

Hi Michael,
Have you been able to figure this out?

6. Thanks for sharing, nice simplified example. Without this temp table know-how, I would’ve wasted another unnecessary column just to lookup.
Cheers!

7. Nicholas Vella Laurenti says:

How does this work, if at all, with hlookup?, i.e. how can you search up with hlookup?

8. Isaac Dart says:

Thank you 🙂

9. Mo says:

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

10. Heather Hoyt says:

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

11. Guilherme says:

Awesome! Thanks, you saved me hours of search and work!

12. Elliot says:

how do you use this formula when referencing an outside wordbook in google sheet?

1. Luis Paliotes says:

Hello I used the following and it worked like a charm for me:
Example:
=vlookup(J1:J,ARRAYFORMULA({‘Accounts Payable’!J:J,’Accounts Payable’!B:B}),2,0),””))

With this logic you can further improve your formula by adding iferror ou by adding it within an Arrayformula.

Example:
=ARRAYFORMULA(iferror(vlookup(J1:J,ARRAYFORMULA({‘Accounts Payable’!J:J,’Accounts Payable’!B:B}),2,0),””))

Hope it Helps

13. Peer says:

What if I need to do this across the google sheets with tons of data and the two column are separated like column A and G. Does it also work with IMPORTRANGE function ? or it work only within the same sheet

14. jose riou says:

Great blog, guy. In that period of Home working, it s a pleasure to have time to read you
Congratulations and many thanks from Paris, France

15. Frank Norrmann says:

Hi Ben , can this be done with HLOOKUP upwards?

1. Martin Naley says:

I’m curious too! Technically, I guess it would still be a VLOOKUP on a synthetic lookup table, where you would choose the two rows and make them the columns in your array. I tried it and it didn’t work, but maybe I did it wrong?

16. Ali Dastgeer says:

How could we use reverse vlookup within two google sheets?

17. Allan Martinez says:

How will this work with an imported sheet via IMPORTRANGE?

18. Diana says:

Amazing! Thank you 🙂