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:

## Can I see an example worksheet?

## 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:

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

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

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.

I can’t use {} formula because it makes two column staked on one column. How to solve it?

Thank you for your help ๐

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

wow, thanks! Helps me a lot! ๐

Thank you, this is so great!

Bravo! Quick and simple explanation.

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), “”)

Hi, Ben! Love your blog!

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

Would love to see this too

Hi Michael,

Have you been able to figure this out?

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

Cheers!

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

Thank you ๐

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

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

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

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

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

Thank you so much for this