VLOOKUP Function in Excel

A very popular, and often used function, VLOOKUP is a fantastic tool in Excel.  The VLOOKUP Function in Excel is used to look for user specified data in column one of a table of data.  As soon as the VLOOKUP finds this data it will return the result on the same row.

The syntax for VLOOKUP is:
=vlookup(lookup_value,table_array,col_index_num,range_lookup)

An example (featured below) would be:
=VLOOKUP(B2,$E$2:$F$20,2,FALSE)

The use of False as the optional range_lookup Argument tells VLOOKUP to find an exact match and is generally needed when looking for a text match. If you omit this, or True, you may receive unwanted results when searching for text that is from an unsorted table. If you use True or omit the range_lookup argument then you should sort your data in ascending order based upon the first column. I use False for around 99% of all of my VLOOKUP Functions.

So lets look at simple example of using a VLOOKUP.

vlookup-dataThe image to the right details two sets of data. On the left we have our three columns; Date, Name, Amount Paid. Note the Amount Paid is empty. A few columns along we have another table of data two columns featuring Name and Paid. What we want to do is use the VLOOKUP to insert the correct amount paid for each person into the third column called Amount Paid.

 

Step 1
vlookup-step-1So we could just tpe the function into cell C2.  But for this example we will use the Insert Function button.  First click in cell C2.  Then just to the left of the formula bar is a small square button that looks like it has an fx on it.   Click this button and it will launch the Function Arguments panel.

 

Step 2
vlookup-step-2Now the Function Arguments panel is open click on the first drop down menu and select All.  This will then populate the menu below with all functions that Excel has.  Once you have used VLOOKUP once it will appear in the Most Recently Used menu.  Scroll through the second menu until you find VLOOKUP and click it and then click OK.

 

 

 

Step 3

vlookup-step-3You should now have window open for the VLOOKUP Wizard.  You will have four boxes that need completing.  Note the box labels are in order according the function syntax.  To complete the Function Arguments you click into each box and then you will be able to select which cells you wish to reference.  So lets complete the boxes.  Click in the first box (Lookup_value) and then click cell B2 as this is the first name in our list we want to look up.  Then click in the next box (Table_array).  This for selecting the second set of data that we want to look in.  So select cells E2 to F20.  Now push F4 on your keyboard.  This is important because it fixes the range.  Next click in the vlookup-step-4Col_index_num and type 2.  This tells the VLOOKUP to return the from the second column.  Then finally click in the Range_lookup box and type False.  Your complete Function Arguments should look like the second image on the right.

 

 

Step 4
vlookup-step-5Once you are happy that your function arguments are correct then you can click OK.  This will then enter the VLOOKUP formula into cell B3.  Click cell B3 and then hover the mouse near bottom right of cell into you get a cross appear.  Click this and pull down on the mouse to cell B20 and this will fill the VLOOKUP function into those cells.  Job done.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *