A comprehensive guide to VLOOKUP Function in Excel

Quick VLOOKUP facts/myths that I have witnessed.. pretty interesting

1. Do you know how to apply a VLOOKUP ? is one of the most asked question in the technical round of excel interview. TRUE
2. If one knows how to apply VLOOKUP, he knows advanced Excel or he is the master of Excel – MYTH
3. VLOOKUP is difficult to learn – BIG MYTH
4. The TRUE/FALSE input at the end of VLOOKUP is the same and gives you the same result – MYTH, we’ll see how and why!

Getting Started with a real life excel case

Consider this data of about 700 + records, where you have Ticket No, Client Name and the Product Code

So where do I use the VLOOKUP ?

What If you were asked the following question –

Hey Sarah/Tim (assuming that is your name 😀 ) can you please give the Client Name and Product Code for the following 75 Ticket Nos ?

1. If you plan to solve this by FIND tool in excel, then I am going to give you a big break
2. If you already know solving this by a VLOOKUP or an INDEX (MATCH) function then I am going to ask you give this a pass and move on to VLOOKUP Tricks

The VLOOKUP grammar (Lets call that Syntax)

There are 4 key ingredients that go inside that function. Let me use plain English to explain

1. lookup_value – What are you trying to find ?
2. table_array – Where are you trying to find ?
3. col_index_num – If vlookup finds it, what corresponding column number should it return ?
4. range_lookup – Method of looking up (approximate match or exact match) ?

Ok so how does this Work ?

Actually it is as simple as I have mentioned above, lets get going with our case, shall we?

1. Lookup_Value

=Vlookup(G4,

We are trying to find the first ticket number

 

2. Table_Array

=Vlookup(G4, B3:D777,    – This is incorrect

Now we are trying to find the ticket number in the range of data specified. Be aware of a small catch here –  you should freeze the data range by pressing F4 Key, because you don’t want the range to move when you pull the formula down, so your ideal formula should look like

=Vlookup(G4,$B$3:$D$777,     – This is correct

 

3. Col_Index_Num

Column Index number is the position number of the result you are looking for, starting from the left. In our case Client name is kept on the second position so our column index number input is 2

 

4. Range_Lookup

In a little survey that I read on the internet sometime ago, it was found that data analysts most times use FALSE as an input for Range_Lookup. When you want your VLOOKUP to give you an exact match answer use a FALSE. Otherwise the data in the first column must be sorted in ascending order for the TRUE input to work properly

Now simply drag down the formula to rest of the cells!

Writing the VLOOKUP for the Product Code

Repeating the above mentioned 4 inputs

1. Lookup Value will be your first ticket number
2. Table_Array will be your source data, where all the ticket numbers exist. Make sure to freeze the entire range using F4 Key
3. Col_Index_num will 3 since the result (product code) is placed in the 3rd column of the data
4. Range_Lookup will be false, since we are applying an exact match lookup

 

More of a video lover ? Watch this

 

Don’t be a Rookie and make these errors

1. Watch the Phantom Extra Space – Vlookups don’t work if the Lookup Value has unwanted extra spaces. Use the Trim function to get rid of any extra spaces in the cell and then apply  the VLookup. =TRIM(Cell Reference)
2. Search Column is the First Column- The VLOOKUP formula only looks in the first column of the select data
3. Not freezing the Table Array Range – The VLOOKUP freezes the range automatically if you are applying it to another workbook, but if working in the same sheet, you have to do it manually using the F4 Key
4. Leaving the Range Lookup Blank – By default excel applies an approximate match (TRUE), make sure if you applying an exact match you specify FALSE as an input
   • =VLOOKUP(lookup_value,table_array,col_index_num) – this will automatically pick up TRUE or approximate match
   • =VLOOKUP(lookup_value,table_array,col_index_num,0) – this also means FALSE
   • =VLOOKUP(lookup_value,table_array,col_index_num,) – if you dont write anything after the comma, this also means FALSE

 

5 Crazy Vlookup Ticks  – Enjoy the show !

Download this file from down below! Share your favorite VLookup tricks using comments !

Download The Ultimate VLOOKUP Guide

The guide contains tricks and tips beyond the conventional VLOOKUP and it’s free – DOWNLOAD THE ULTIMATE VLOOKUP GUIDE (pdf)