Excel VLookUp(Vertical Look Up) is a powerful yet simple function that can help you to find values faster and smarter when you have huge data at your disposal. The VLOOKUP function is often seen as a complex function since most of us do not understand how it actually works.
Today let’s quickly see how a VLOOKUP can be used with an example. Before using any function we should know its syntax, so let’s get started with it first.
Syntax of Excel VLOOKUP Function
The syntax of VLOOKUP function is given below:
As you could see from the above formula VLOOKUP takes 4 parameters, let us see what each one of them is for.
- lookup_value is the actual value for which you are trying find data.
- table_array is the data source against which your query will be executed.
- col_index_num is the column in the table_array from which you would like to fetch the data.
- range_lookup has two values, TRUE or 1 for Approximate match & FALSE or 0 for Exact match
Do not worry if the explanation for each of the above parameters doesn’t make any sense, you will understand it better in the next section.
How to Use Excel VLOOKUP
I always believe that hands-on example will help us to understand a concept better. So let’s see how VLOOKUP is used with a simple example.
A snapshot of the data I have used for this example is shown below.
Though this may not be the actual scenario in the real world, it will help us to understand the concept better.
- Download the sample workbook that I have created for you from here.
- Open the Source Data sheet and you should see that there are around 60 test users and for each user we have their names, availability of passport, base location and languages that they know.
- Now if we would like to know the passport availability of a particular user then we can find it quickly using a VLOOKUP. Let’s see how to do that.
- In the Source Data sheet, in K9 cell start typing =VL and you should see an intellisense that populates the VLOOKUP function, now just hit the TAB key and you should see the open parenthesis automatically opened up for you. Now you have to fill in the parameters to complete the VLOOKUP function.
- For the first parameter lookup_value select the J9 cell and enter comma which will move the cursor to the next parameter.
- For the table_array parameter, select the cells C5 to F65 and enter comma to move to the next parameter. Note: The column that will be matched against the input that you provide should be the first and left-most column in your selection.
- Since we want to know the availability of passport for a particular user, provide the value as 2 and enter comma in the col_index_num parameter. Since the second column has the passport availability status we have entered the value as 2. If you want to get the language that a particular user knows then you will have to enter 4.
- The final parameter of the VLOOKUP function is the range_lookup. Enter TRUE to find the Approximate Match, close the parenthesis and press Enter.
- Initially you should see that K9 cell has the value #N/A, this is because you haven’t entered any value in the cell J9 which is the input for the VLOOKUP.
- Now go ahead and enter the value Cobb in the cell J9 and press enter. You should now see the passport availability changed to No.
I can hear your voice which says: You can accomplish the same with a simple CTRL + F, Yes you can, but if you have a new data source and you would like to compare it with the old one then that’s where VLOOKUP becomes more powerful. Let’s see how to do that.
Compare and Extract using VLOOKUP
Let’s assume that you have got the latest data for the users that you have in Source Data Sheet and you would like to know which have moved to new locations. Let’s see how to use VLOOKUP for this scenario.
- I have copied the values from Source Data into Users New Location sheet.
- Now in F5 cell enter the VLOOKUP formula as shown below:
- Now you should get the original locations of these users in the cell F5. Select the cell F5 and move cursor to the right bottom of the cell and you should see a cross hair symbol.
- Now double click on it which will cascade the formula to the below cells.
- Now all you have to do is write a simple IF function in cell G5, place the below formula in G5
Now you should get all the users whose location has changed.
Though there are better ways to do what I have done here, I feel that this is the simpler way to learn VLOOKUP if you are a beginner. Got something to add ? Feel free to spice this up.