Buzz
VLOOKUP stands out as one of the most frequently used functions in Excel. This article provides a detailed guide on how to use and offers specific examples illustrating the application of the VLOOKUP function.
1. Syntax and Usage of VLOOKUP Function
- The VLOOKUP function is designed to search for values based on conditions, with the search conducted across columns.
- Syntax of VLOOKUP Function:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Where:
+ lookup_value: The value to be searched, a mandatory parameter.
+ table_array: The data range to search for values, a mandatory parameter.
+ col_index_num: The column containing the desired value to be searched, a mandatory parameter.
+ range_lookup: The search type, range_lookup = 1 equivalent to True -> relative search, range_lookup = 0 equivalent to False -> absolute search.
2. Simple Example Using the VLOOKUP Function
2.1 Example 1
- Using the following data table, apply the VLOOKUP function to fill in allowances for employees (using VLOOKUP):
- In the cell where the allowance needs to be calculated, input the formula: =VLOOKUP(E4,$B$15:$C$18,2,1) (note to use absolute values for the data range containing the lookup values):
- Press Enter to get the result:
- Similarly, copy the formula for the remaining values to get results:
2.2 Example 2
- Input information about employees' education levels based on employee codes, using the education table.
- To obtain the education level of employees based on the first character in the employee code, use the Left() function to extract the first character and then search for that character in the education table.
- In the cell where calculation is needed, input the formula: =VLOOKUP(LEFT(B4,1),$F$15:$G$18,2,0)
- Press Enter to get the result:
- Copying the formula for the remaining values shows an error message:
- Due to not using absolute addressing for the data range containing the lookup values, the VLOOKUP function throws an error. Rectify by selecting the address and pressing F4 to switch to absolute addressing:
- Finally, copy the formula for the remaining values to get results:
2.3 Example 3
Input information about department and position of employees based on the department and position table below:
- To extract the department based on the first character in the employee code and the position based on the last character, use VLOOKUP for department (as values are arranged in columns) and HLOOKUP for position (as values are arranged in rows). Concatenate both values as they share the same column.
In the cell where information needs to be filled, input the formula: =VLOOKUP(LEFT(B4,1),$D$16:$E$19,2,1)&' - '&HLOOKUP(RIGHT(B4,1),$F$15:$G$19,2,1)
- Press Enter to get the result (remember to set absolute addressing for the range containing lookup values):
- Similarly, copy the formula for the remaining values to get results:
Here is a detailed guide on how to use the VLOOKUP function with specific examples. I hope this helps you. Best of luck!
