VLOOKUP function - Usage and detailed examples
VLOOKUP is one of the most used Excel functions. The following article details how to use and detailed examples when using the VLOOKUP function.
1. Syntax and usage of VLOOKUP function
- VLOOKUP function is a search for conditional values, the search is performed on columns.
- VLOOKUP function syntax:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Inside:
+ lookup_value: The value you want to search, is a required parameter.
+ table_array: The data area you want to search for values, is a required parameter.
+ col_index_num: Column containing the value to search, is a required parameter.
+ range_lookup: Search type, range_lookup = 1 is equivalent to True value -> relative search, range_lookup = 0 absolute search is equivalent to false value.
2. Simple example using VLOOKUP function
2.1 Example 1
- The following data sheet is available, based on the allowance allowance sheet filled out for employees (using Vlookup function):
VLOOKUP function - Usage and detailed examples Picture 1
- In the cell to calculate the formula entry allowance: = VLOOKUP (E4, $ B $ 15: $ C $ 18,2,1) (note the absolute value for the data range containing the search value):
VLOOKUP function - Usage and detailed examples Picture 2
- Press Enter to get the results:
VLOOKUP function - Usage and detailed examples Picture 3
- Similarly copying the formula for the remaining values results:
VLOOKUP function - Usage and detailed examples Picture 4
2.2 Example 2
- Enter employee qualifications based on the employee code, if the employee is on the qualification table
VLOOKUP function - Usage and detailed examples Picture 5
- Here to get the employee qualifications through the first character in the staff code. So, you need to use the Left () function to get the first character in the code and look for that character in the level table.
- In the cell to calculate, enter the formula: = VLOOKUP (LEFT (B4,1), $ F $ 15: $ G $ 18,2,0)
VLOOKUP function - Usage and detailed examples Picture 6
- Press Enter to get the results:
VLOOKUP function - Usage and detailed examples Picture 7
- Copying the formula for the remaining values displays the error message:
VLOOKUP function - Usage and detailed examples Picture 8
- Because of not placing an absolute address for the data area containing the search value, VLOOKUP function reports an error, executes the address highlight, and press F4 key to change the address to the absolute address:
VLOOKUP function - Usage and detailed examples Picture 9
- Finally copying the formula to the remaining values results:
VLOOKUP function - Usage and detailed examples Picture 10
2.3 Example 3
Enter the department information and employee ranks based on the department table and category below:
VLOOKUP function - Usage and detailed examples Picture 11
- Here to get the staff's department based on the first character in the kernel code. To get a rank based on the last character in the employee code. But here the department values are arranged in columns so VLOOKUP should be used while rows value should be used in HLOOKUP . Departments and ranks should put together 1 column and combine 2 values into 1.
In the field you need to enter the formula: = VLOOKUP (LEFT (B4,1), $ D $ 16: $ E $ 19,2,1) & "-" & HLOOKUP (RIGHT (B4,1), $ F $ 15: $ G $ 19,2,1)
VLOOKUP function - Usage and detailed examples Picture 12
- Press Enter to get the results (remember to pass the absolute address for the search value container):
VLOOKUP function - Usage and detailed examples Picture 13
- Similarly copying the formula for the remaining values results:
VLOOKUP function - Usage and detailed examples Picture 14
The above is a detailed guide on how to use the VLOOKUP function and some specific examples that hope to help you. Good luck!
You should read it
- How to fix VLOOKUP error in Excel
- VLOOKUP function to use and specific examples
- How to combine Sumif and Vlookup functions in Excel
- How to automate Vlookup with Excel VBA
- Vlookup function in Excel
- Use VLOOKUP to join two Excel tables together
- How to use VLOOKUP Function in Excel
- How to combine Vlookup function with If function in Excel
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
- 6 useful functions in Google Sheets you may not know yet
- How to use the IF function with VLOOKUP (examples and how to)
- How to use Vlookup function between 2 sheets, 2 different Excel files
May be interested
Steps to make presentation reports by Powerpoint fast and beautiful for beginners
How to use effects in PowerPoint
Use effects for tables and charts in PowerPoint
Use SmartArt effects in PowerPoint
Create and use effects for audio and video in PowerPoint
Use effects with images and shapes in PowerPoint 2016