The most frequently used formula in Excel is Vlookup. Every body thinks that they know fully about it but very few knows. Now we will walk through step by step.

What is Vlookup?

Vlookup mean Verticle Lookup. There are two types of tables VIZ. Verticle table and Horizontal Table. See the pic. If your data is in Verticle table format we use Vlookup otherwise Hlookup.

What is the Syntax for Vlookup?

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) . Article title is on this range_lookup argument.  See the below Picture.

Why we use Vlookup?

For instance, In a company there are 10,000 employees. You want to extract details of 500 employees. You have two spread sheets, one sheet contains details of all 10,000 employees information such as Emp_id, Basic,DA etc. Another sheet contains only IDs of those 500 employees.

Now how to extract details such as Basic,DA etc for these 500 employees? Here we use Vlookup keeping Range_lookup as “0″. As we are lookuing for exact matches. see pic.

When we will use “1″ in Vlookup for Range_Lookup argument?
When we are looking for nearest matches we use 1. When we use 1, if the exact match is not found it will bring the second highest value. For this reason whenever we use 1 in vlookup, lookup values in the table_array will  be sorted in the ascending order. Classic example for using 1 is Debtor’s Aging report (must see).Whenever we use Class intervals we can use 1.

What is that $ symbols sorrunding the range?

That is kept to keep the range constant. Look at this article on relative reference and absolute reference for better understanding.

Things that you must remember about Vlookup?

1.Lookup_Value must be always found in the Col_Index_num 1 of Verticle Table. Suppose if you have lookup_value in the second or third column, you must make that column as first column of the Table.

2.Don’t forget to put $ symbols around the Table range. Vlookup works even without $ symbols but results may not be correct.

3.Remember if any lookup_value (in our case Emp_ID) gets repeated in the table_array, Vlookup always brings the values corresponding to the first Lookup_value. And it will totally ignore the values corresponding to the other lookup_values. If you have repeated items in the first column, to work around, you have to use pivot tables and then apply Vlookup formula or use Sumproduct formula you need not put Pivot table.

4.Whenever we use 1 in Vlookup we must arrange lookup value (Emp_ID) in the Table_array in the ascending order

Tweet This