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!