In this article, you will see the exciting and interesting use of one of the powerful Excel VLOOKUP function. The article covers real life situations to make the learning as easy as possible for you.
If there is talk of most popular function in Microsoft Excel, VLOOKUP is going to be one of the function top in the list. Any Excel training course or job without the inclusion of the VLOOKUP function is always considered incomplete and in this tutorial you are going to learn about this awesome Excel Function.
Microsoft Excel VLOOKUP Function
The VLOOKUP function is used to perform a vertical search for a value in the leftmost column of a table or range of cells and returns a value in the same row from the specified column index number.
The letter “V” stands for “vertical” in VLOOKUP function. Usually the need of VLOOKUP arises when we have some common values in different data tables and we need to pick certain values from one table to another in a vertical fashion. VLOOKUP always return the data from the right most columns with lookup values having in the first column of a table.
You must have records in vertical order and value to be picked must be on right and lookup value must be in the first column of table.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup] )
- lookup_value: The value that you want to find in the first column of the data table (second argument). It can either be supplied directly, by referring to a cell in the worksheet or value returning by other Excel function or formula.
- table_array: The table from which you want to retrieve a value. Your table array must start with the column containing the lookup value.
- col_index_num: It is the column in the table_array from which the value should be returned.
- [range_lookup]: [Optional] A Boolean value entered either as TRUE/FALSE or 0/1 to determine whether you want to search for an exact match or an approximate match.
- This argument is an optional but very important. If you omit this argument or enter as TRUE or 1, an approximate match will return.
- If supplied as 0 or FALSE as the last argument, an exact match will return.
Examples: – VLOOKUP Function
Once you understand the arguments of VLOOKUP function and its’ basic functionality, applying it in the spreadsheet becomes much easier. The following live examples would make you familiar with VLOOKUP function and its’ uses in different circumstances.
How to lookup Email ID with VLOOKUP
Suppose there is a table in our worksheet on left and somewhere on the right side or below in the same spreadsheet, we want to retrieve some information from the table.
In the following screenshot, there is lookup value given in cell B2 and in the corresponding cell C2, we need to get the email id with respect to lookup vale from the table given below. To get the same, let’s enter the following function in cell C2 as follow:
=VLOOKUP(B2, A4:C9, 3, FALSE)
How does this function works?
- As the VLOOKUP rule says, the lookup value must be in the first column of the table and if you look at the table, the first column contains the lookup value.
- VLOOKUP finds the lookup value at 4th position in column ID and then jumps 3 columns to the right in the same row to get email id with exact match.
Example 1: VLOOKUP with Exact Match
VLOOKUP is most often used in exact match mode. And that is because you might not want VLOOKUP to pick the information of X person against Y person’s name. If the table does not contain the lookup value in the first column, it should simply return an error or saying, “Value not found” instead of seeing incorrect value in the cell.
In this example, we have to pick up Total Assets and Email ID basis on Unique ID given in cell. And if you take a closer look at the data, you would notice that the lookup value this time is not given in first column but in second column of table array. Which means, the table_array argument must start from the 2nd column i.e. Unique ID.
In this case, move on to cell I5 and write the function as follow:
Lookup value i.e. Unique ID (I3) found at 5th position in first column of table array (C3:F9) and then within same row, Total Assets found in 3rd column of table array which holds the value as $190,033.
Tip: Always use absolute cell reference ($ sign) to fix the cell reference/range of cells when you want to copy your VLOOKUP function to other cells. It prevents the reference from changing when copied to new location. You can insert dollar sign by pressing SHIFT + 4 keyboard before column letter and row number or use F4 (function) key to fix the reference.
Example 2: VLOOKUP with Approximate Match
What is an approximate match? An approximate match is performed when the last argument of function is either omitted or set to TRUE/1. At first, function looks for an exact match but when lookup value is not found exactly, it returns the nearest value that is less than lookup value. The first thing you should do is sort the first column of the table array in ascending order while performing approximate match.
Once you are done with the sorting, you are ready to use an approximate match in VLOOKUP.
In this example, we need to get ‘Salary Band’ basis on given salary bracket range (refer to screenshot below). The first solution that comes to mind is writing nested IF formula and get the ‘salary band’ easily. But that could be time consuming task as if there are too many criterion to be checked, a small typo can lead to big trouble.
Write the following formulas in cell B2 to retrieve the ‘Salary Band’:
The above formula is returning the closest salary band that is less than the lookup value (salary column). For example, when we looked for $1,396 from the salary column, 1400 is very closest value in the table array however, it is not less than the lookup value hence the function went back to previous record in the table array that is 1200 which is lesser than the lookup value also, function retrieved the value from 2nd column and returned.
I am hoping the taken examples in this article would be helpful for you to understand the basic use of VLOOKUP function. The following key notes are good and handy to remember the best use of VLOOKUP function.
- Function default returns approximate match if the last argument is not supplied.
- VLOOKUP cannot lookup from right to left. The lookup_value must be in the first column of the lookup table (table_array) to retrieve the value from the right side columns.
- #N/A error is returned by the VLOOKUP function when lookup value is not found in the first column of the table_array argument.
- Function returns #VALUE! error when col_index_num argument is supplied as less than 1
- When the 3rd argument (col_index_num) contains the value greater than the available number of columns in table_array, function returns #REF! error.
- Use IFERROR function to control the #N/A error.
- VLOOKUP is in case sensitive and can handle wildcard characters to return partial matches.
- Range_lookup argument decides which type of match should be performed.
- Default setting will execute an approximate match however an exact match will be returned if the lookup_value exist in the leftmost column of the second argument.
- The table (table_array) must be sorted in ascending order based on data in the first column of the table when performing with approximate match.
- Boolean value TRUE can also be supplied as 1 and FALSE as 0.
INDEX | MATCH | HLOOKUP| LOOKUP