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):
- 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):
- Press Enter to get the results:
- Similarly copying the formula for the remaining values results:
2.2 Example 2
- Enter employee qualifications based on the employee code, if the employee is on the qualification table
- 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)
- Press Enter to get the results:
- Copying the formula for the remaining values displays the error message:
- 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:
- Finally copying the formula to the remaining values results:
2.3 Example 3
Enter the department information and employee ranks based on the department table and category below:
- 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)
- Press Enter to get the results (remember to pass the absolute address for the search value container):
- Similarly copying the formula for the remaining values results:
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 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
May be interested
- Steps to make presentation reports by Powerpoint fast and beautiful for beginnersthe following article is detailed instructions steps to make presentation reports in powerpoint fast and beautiful.
- How to use effects in PowerPointhow to use effects in powerpoint 2016. in slide, there are many objects that need to use effects. however, for each different object, use different effects.
- Use effects for tables and charts in PowerPointthis article introduces you to how to use effects for tables and charts in powerpoint 2016. after creating tables and charts, you make creating effects that appear for the following objects.
- Use SmartArt effects in PowerPointthis article shows how to use the smartart effect in powerpoint 2016. to create a smartart object, click the insert - smartart tab - select the smartart style you want.
- Create and use effects for audio and video in PowerPointthe following article shows how to insert, use and customize effects with audio and video in powerpoint. to insert effects for sound, you first need to perform the operation of inserting sound into the slide.
- Use effects with images and shapes in PowerPoint 2016in the previous article i introduced how to use effects with text in powerpoint, this article you will be familiar with how to use effects with images and shape objects in powerpoint.