Last updated on April 16th, 2023
Estimated reading time: 4 minutes
What is VLOOKUP in Excel?
VLOOKUP stands for Vertical Lookup. As the name specifies, VLOOKUP is a built-in Excel function that helps you look for a specified value by searching for it vertically across the sheet. Use VLOOKUP when you need to find things in a table or a range by row.
Use of VLOOKUP:
A VLOOKUP is an Excel function that allows you to look up data automatically in a separate table. The focus of using a lookup function is to make the data analysis quick, which works well with large amount of data.
VLOOKUP Formula:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)Formula Details :
- Lookup_value: Select the cell where search values will be entered. (In simple words/language : Tell the function what to lookup)
- Table_array: The table range, including all cells in the table.(In simple words/language :Tell the function where to look)
- Col_index_num: The data which is being looked up. The input is the number of the column, counted from the left:(In simple words/language : Tell Excel what column to output the data from)
- Range_lookup: TRUE if numbers (1) or FALSE if text (0).(In simple words/language :Precise or approximate match)
Exact match vs approximate match :
- FALSE to search for an exact match, that is, if an exact match not found, then it will return an error.
- TRUE to find an approximate match, that is, if an exact match is not found, use the closest match below the lookup value.
PRACTICAL EXAMPLE OF VLOOKUP :
VLOOKUP Exact Match Example :
In the example above, we are using the VLOOKUP function to find the value of the exact match of Employee ID from the given table.
- First: – We set the first parameter as the lookup value, which is the cell K7.
- Second: – We specify the location of the table in the second argument. As you can see, the table location is B7:G18.
- Third: – We specifies the Column Index number. This tells us what value should be returned from the row that we are looking up for. In the example, the name column is 2. Same as for Salary details the column index number is 6.
- Last: – The value is set to FALSE for the VLOOKUP function to return an exact match for the value.
- Note: – An N/A error is displayed in case the exact value is not found.
Result of VLOOKUP Formula:
- To apply the formula to other Employee ID just Drag down the formula.
- Repeat the process for other columns like Salary Column just change the column index number to 6.
VLOOKUP Approximate Match Example :
In Excel, VLOOKUP with an approximate match (TRUE) searches for the next largest value that is less than your specific lookup value in other words the value closest to and inferior to the value you are looking for.
In this example, we’ll find someone’s grade based on their scores :
Very Important Note :- The approximate match works only if the values in the search range are in ascending order. To use the VLOOKUP function to get an approximate match value, your first column in the table must be sorted in ascending order, otherwise it will return a wrong result.
- First : Enter =VLOOKUP Formula in cell E12, where you want the Grade to appear.
- Second : Enter the Lookup value D12, which contains the Scores you want to look for.
- Third : Enter the Search range B3:D7 , which is the data range containing all the Score and Grade values.
- Forth: Enter Column number 2, as the Grade column is the 2nd column of the Search range.
- Fifth : Enter TRUE to look for an exact match.
- Sixth : Copy the formula to rest of the Grade column.
- Table Array changing when copying formula : Use the F4 key + Fn Key
- The F4 key is the easiest way to lock cell references. Simply select the cell or range of cells that you want to lock, then press the F4 key.
- Excel will automatically add the $ sign to the appropriate places in the cell reference. One advantage of using the F4 key is that it’s quick and easy.
Same process for Result. Just Enter Column number 3, as the Result column is the 3rd column of the Search range.