In this lesson we will learn the ins and outs about the VLOOKUP function in Excel. This is one of the most commonly used functions in Excel. VLOOKUP searches for a value in the first column of a table with information organized vertically. From the row with which the value matches, it retrieves a value from the specified column.
=VLOOKUP(value, table, col_index, [range_lookup])
- value – The value to look for in the first column of a table.
- table – The table from which to retrieve a value.
- col_index – The column index in the table from which to retrieve a value.
- range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.
Note :
- The range_lookup can be provided in binary 1(TRUE) or 0(FALSE).
- range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
- Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.
- If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
- When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
- If range_lookup is TRUE (the default setting) make sure that lookup values in the first column of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
- If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.
Most of the time you need to look for an exact match when you use the VLOOKUP function. Let’s understand more with some simple examples. Below we have 2 tables. One contains the list of products and their height and weight information. Let’s say this is table A,containing column A,B & C.
There is another table which contains the Price information for the list of products. Let’s say it’s table B,containing column F & G.
Now we need the price in table A. In column D we have used the VLOOKUP function to retrieve the price for each product from table B. In cell D2 the VLOOKUP function is mentioned as follows.
=VLOOKUP(A2,F$2:G$35,2,FALSE)
- Here A2 is the value we need to find. This will be changed as A3, A4 and so on, while dragging the formula for the following cells in column D.
- The table from where the value needs to be retrieved is F2:G35. Here we used $(F$2:G$35) so that the table index won’t change while dragging the formula for the following cells in column D.
- We need to retrieve the price which is in 2nd column of table B. Hence we put 2 in column index argument of VLOOKUP function.
- In last argument we put FALSE which denotes we need an exact match here
We have used the same tables in the below example but only changed the last argument in VLOOKUP function as TRUE.
=VLOOKUP(A2,F$2:G$35,2,TRUE)
Now as you can see below, for every products in table A we have price in column D retrieved based on the non-exact match to the nearest value(Products) in table B. Here the first row of table B is sorted in ascending order.
For example, the price of product ‘Console table’ has been retrieved as 56. As the product ‘Console table’ in table A has been nearest matched with the product ‘Coat Hook’ in table B since the first 2 characters of both products are the same. And the price for ‘Coat Hook’ in table B is 56 so it has been fetched for ‘Console table’ in table A.
Please notice, if the exact match is found, it will always return the respective value for the exact match even though the 4th argument of VLOOKUP function is TRUE.
In our last example we have introduced another column in table B containing the category of the products and fetched the same in column E of table A for the exact match of products with the VLOOKUP function as follows.
=VLOOKUP(A2,F$2:I$35,3,FALSE)
As you can see the column index has been updated here as 3 as the category column(column I) is the 3rd column of table B. Also we have updated the table range(table B) as F$2:I$35 here.
Please Note, the VLOOKUP function is Case-Insensitive. In the above example we have shown the products in the first column(column G) of the table B has been mentioned all in upper case but the products in first column(column A) in table A which need to be looked for, are in proper case but this didn’t make any difference in lookup with VLOOKUP function.
Now we will learn to use VLOOKUP with multiple criteria. Let’s say in our below example we have a source table containing the number of Flu patients for Summer and Winter season for 5 US states. Let’s call it as table A. Now in table B we need the count of Flu infected patients for each of the 5 states in Winter season only.
Now if we use VLOOKUP straightforward as we have described above it will always return the count of Flu patients in summer as for each state that is the first match it will get.
Here we will use a technique to prepare the unique key in table A. We will introduce a column with a name let’s say ‘Unique Key’ as follows. There we will use the below formula to populate column C(Unique key) consisting of the values from both A and B column.
=A2&”|”&B2
This is the formula we used in cell C2 and then dragged for the following cells.
Here we have used pipe (|) delimiter. You can use any other delimiter as per your choice. Now each value in column C is unique.
Now we can use the below VLOOKUP formula to get the correct data in column I of table B.
This is the formula used in cell I2 and then dragged for the following cells.
=VLOOKUP($H2&"|Winter",$C$2:$D$11,2,0)
Here in place of ‘Winter’ if you use ‘Summer’ it will return the Flu infected patient count for Summer. Here also in the first parameter of VLOOKUP function we created the unique key by merging the state with the desired season delimited by pipe(|) same as in column C. Now based on this primary key we are looking for the respective value from table C2:D11. See below we have got the correct count for Winter in table B.
You can also check here to understand the VLOOKUP function in Google Sheet.
You can also check here to understand the HLOOKUP function in Google Sheet.
You can also check here to understand the HLOOKUP function in Excel.
You can also check here to understand the LOOKUP function in Google Sheet.
You can also check here to understand the LOOKUP function in Excel.