A few weeks ago David T asked me to help him understand a VLOOKUP formula in a workbook he’d inherited from a colleague who had left his company.
It was a VLOOKUP formula like nothing I’d ever seen before so I thought I’d share it with you.
Drum roll…..
=VLOOKUP(E2&F2,IF({1,0},First_Name&Last_Name,Grade),2,FALSE)
David’s question was ‘what’s the IF({1,0},… doing’?
First here’s the Excel workbook used in this tutorial if you want to download it. Note: this is a .xlsx file. Please ensure your browser doesn’t change the file extension on download.
Ok, before we dive in and try to understand the IF({1,0} we'll start by remembering the syntax for VLOOKUP:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s look at an example (I’ve recreated some dummy data as David didn’t send me his file):
Note: I’ve given the columns A, B and C the following named ranges which are referenced in the formula:
- A2:A5 = First_Name
- B2:B5 = Last_Name
- C2:C5 = Grade
The formula in cell G2 is looking up the names in E2 & F2 and finding matching values in column A (First_Name), & B (Last_Name) and then returning the result in column C:
How Does it Work?
Firstly this is an array formula so it must be entered with CTRL+SHIFT+ENTER.
- The formula uses an ampersand (&) to concatenate/join the lookup_values in E2 & F2 like so:
- The IF function creates a matrix, which is the table_array argument for the VLOOKUP formula which consists of two columns.
- Lastly the col_index_num simply tells Excel to return the value in the second column of the table_array i.e. the Grade.
=VLOOKUP(JimBrown, IF({1,0},First_Name&Last_Name, Grade),2,FALSE)
Remember the syntax for the IF function is:
IF(logical_test,[value_if_true],[value_if_false])
The {1,0} matrix are numerical equivalents of TRUE and FALSE and are the logical_test argument for the IF Function.
Note how the value_if_true argument also uses the ampersand to join the First_Name’s & Last_Name’s together.
In the formula it evaluates like this:
=VLOOKUP("JimBrown",{"JimBrown","A";"BillyJean","A";"SarahJones","B";"BillyRay","C"},2,FALSE)
Where commas separate columns, and semi-colons separate rows.
You might find it easier to visualise the table_array like this:
Special thanks to Roberto for helping me decipher this formula.
VLOOKUP vs INDEX & MATCH
As I said, I’ve never seen it done this way. I would have used INDEX & MATCH.
Remember VLOOKUP’s sibling is INDEX & MATCH. Some might say INDEX & MATCH is the better looking/more elegant sibling. What VLOOKUP can do, INDEX & MATCH can usually do better.
Here’s how:
=INDEX(Grade,(MATCH(E2&F2,First_Name&Last_Name,0)))
Also entered as an array formula with CTRL+SHIFT+ENTER.
What Do You Think?
Whilst I enjoyed learning this VLOOKUP & IF function trick I still prefer INDEX & MATCH for this type of challenge. It’s not only a shorter and more efficient formula than VLOOKUP & IF, I think it’s also easier to understand.
Have you seen this before? What do you prefer; VLOOKUP & IF or INDEX & MATCH?
Let me know in the comments below, and if you liked this please share it on LinkedIn, Twitter, +1 on Google or Facebook using the buttons below.
Harold says
Would this have been easier by inserting an extra column and concatenating the first and last name? Then using vlookup or index+match?
Mynda Treacy says
Hi Harold,
Sure, you could do that for both tables, but often people want a solution that doesn’t require extra manipulation of the data.
Mynda
manu chand says
i want in my excel sheet remarks collom 0= CLEARE, IF NOT 0 = NOT CLEAR
LIKE PENDING AMOUNT 500= NOT CLEAR. 0 =M CLEAR HOW?
Catalin Bombea says
Hi Manu,
Try a simple IF formula:
=IF(A1=0,"Clear","Not Clear")If you copy this formula down as needed, you will get the “Clear” result only for cells from A column that are 0 or empty.
Cheers,
Catalin
Diana Kretzschmar says
Another Question: What does CTRL+SHIFT+ENTER do? It is said, that it’d be entered with it b/c it’s an array formula?
Mynda Treacy says
Hi Diana,
An array formula requires entering differently to a regular formula. i.e. with CTRL+SHIFT+ENTER. You can read more about array formulas here.
Kind regards,
Mynda.
Diana Kretzschmar says
Mynda,
on #3, “Lastly the col_index_num simply tells Excel to return the value in the second column of the table_array i.e. the Grade.” – did you mean the second or third column?
Diana
Mynda Treacy says
Hi Diana,
That’s a great question. I meant the second column and this is because we are using the & to join column A and B together so from Excel’s point of view they are the first column and column C is the second column.
I recomment you use the Evaluate Formula tool to follow the logic and evaluation steps of this formula so you can see how it is working under the hood. Here is a video I recorded on troubleshooting formulas and understanding how they work.
Kind regards,
Mynda.
jiwan singh says
Dear sir/madam
when i am puting vlookup farmula in worksheet its not working,is it running with vba code ? if yes plese send us also vba code
thanks
jiwan singh
Mynda Treacy says
Hi Jiwan,
It doesn’t require VBA but it is an array formula which means you need to enter it by pressing CTRL+SHIFT+ENTER.
You can read more on array formulas here.
Kind regards,
Mynda.
Eric Stut says
Hi Mynda,
You wrote: Lastly the col_index_num simply tells Excel to return the value in the second column of the table_array i.e. the Grade.
The grade is in column C, so isn’t the third column of the table array?
Thanks in advance for your reaction.
Kind regards, Eric
Mynda Treacy says
Hi Eric,
Good observation, but because we are joining the first and last names together inside our formula, they become the first column and the grade becomes the second column.
When you look at the formula as it evaluates we can see the comma separates the data into columns and the semicolon onto a new row:
=VLOOKUP("JimBrown",{"JimBrown","A";"BillyJean","A";"SarahJones","B";"BillyRay","C"},2,FALSE)If we were to view the lookup_array above in a tabular format it would look like this with two columns of data:
JimBrown A
BillyJean A
SarahJones B
BillyRay C
I hope that helps.
Kind regards,
Mynda.
louay says
Dear Mynda,
You are Awesome!
thank you for your share ..
louay
Mynda Treacy says
Thank you, Louay 🙂
Edwin Kumar says
Great little trick with vlookup. I haven’t done much with arrays on my reports but it looks very powerful way of referencing data. I prefer index & match 🙂
Mynda Treacy says
Thanks, Edwin 🙂
effendi Wijaya says
thank you, it’s great post. i never thinking this way before, i prefer use index and match easier than vlookup & if.
Mynda Treacy says
Thanks, Effendi 🙂
pmsocho says
Awesome. Good to know about that. I have never seen such construction before 🙂
The similar formula with CHOOSE instead of IF could be:
{=VLOOKUP(E2&F2,CHOOSE({1,2},First_Name&Last_Name,Grade),2,0)}
CHOOSE is more fexible than IF because you can create an array with more than 2 columns 🙂
Recently, I explained one of my students VLOOKUP/CHOOSE and INDEX/MATCH/. She went with INDEX/MATCH because she said it was easier to understand 🙂
Mynda Treacy says
Cheers, Pmsocho 🙂 I like your CHOOSE option too.
I wrote a tutorial on VLOOKUP and CHOOSE here, but with just a single lookup value.
Kind regards,
Mynda.