VLOOKUP: You can use the VLOOKUP function to search the first column of a range of cells, and then
return a value from any cell on the same row of the range. For example, suppose that you have a list of
employees contained in the range A2:C10. The employees Code numbers are stored in the first column
of the range, To Make the following Employee Data Base in Sheet1. Where DA 25%, TA 35%, and HRA
54% of Basic Salary.
A B C D E F G
1 E_Code E_Name Basic DA TA HRA Total
2 DET1001 Rakesh
3 DET1002 Mohan
4 DET1003 Sohan
5 DET1004 Ranjeet
6 DET1005 Akash
7 DET1006 Raju
8 DET1007 Pawan
9 DET1008 Anil
10 DET1009 Mahesh
11 DET1010 Deepak
12 DET1011 Anuj
13 DET1012 Praveen
14 DET1013 Kamlesh
15 DET1014 Dilip
16 DET1015 Anil
17 DET1016 Ranjeet
18 DET1017 Pooran
19 DET1018 Kabir
20 DET1019 Komal
21 DET1020 Ramesh
22 DET1021 Kiran
23 DET1022 Kishore
24 DET1023 Dinesh
25 DET1024 Ashok
If you know the employee's Code number, you can use the VLOOKUP function to return either the
department or the name of that employee. To obtain the name of employee Code DET1015, you can use
the formula =VLOOKUP(A16, A2:G25, 2, 0). This formula searches for the Code DET1015 in the first
column of the range A2:G25, and then returns the value that is contained in the Second column of the
range and on the same row as the lookup value ("Anil").
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values
are located in a column to the left of the data that you want to find.
Syntax:
VLOOKUP(lookup_value, table_array, col_Index_num, 0)
return a value from any cell on the same row of the range. For example, suppose that you have a list of
employees contained in the range A2:C10. The employees Code numbers are stored in the first column
of the range, To Make the following Employee Data Base in Sheet1. Where DA 25%, TA 35%, and HRA
54% of Basic Salary.
A B C D E F G
1 E_Code E_Name Basic DA TA HRA Total
2 DET1001 Rakesh
3 DET1002 Mohan
4 DET1003 Sohan
5 DET1004 Ranjeet
6 DET1005 Akash
7 DET1006 Raju
8 DET1007 Pawan
9 DET1008 Anil
10 DET1009 Mahesh
11 DET1010 Deepak
12 DET1011 Anuj
13 DET1012 Praveen
14 DET1013 Kamlesh
15 DET1014 Dilip
16 DET1015 Anil
17 DET1016 Ranjeet
18 DET1017 Pooran
19 DET1018 Kabir
20 DET1019 Komal
21 DET1020 Ramesh
22 DET1021 Kiran
23 DET1022 Kishore
24 DET1023 Dinesh
25 DET1024 Ashok
If you know the employee's Code number, you can use the VLOOKUP function to return either the
department or the name of that employee. To obtain the name of employee Code DET1015, you can use
the formula =VLOOKUP(A16, A2:G25, 2, 0). This formula searches for the Code DET1015 in the first
column of the range A2:G25, and then returns the value that is contained in the Second column of the
range and on the same row as the lookup value ("Anil").
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values
are located in a column to the left of the data that you want to find.
Syntax:
VLOOKUP(lookup_value, table_array, col_Index_num, 0)
