VlookUp stands for vertical column look up. This formula is used to find the value in the column vertically.
e.g.
We have a table of employee details, where employee id, first name, last name, email id and contact number data is there. The data is in column A, B, C, D and E respectively.
This table contains data for 10000 employees.
Problem : We need to get the email id and contact number data for 43 employees in the output.
The only input we have is employee id in column H.
Solution - Vlookup
below is the formula which can be used to get the employee details -
Get email id
=VLOOKUP(H2,A:E,4,0)
how it works ?
1. here H2 is the reference employee id for which we need to find the data
2. A:E is the table range where our data is present
3. 4 is the number of the column starting from column A, in which our data is present
4. 0 is for exact match
Similarly use the below formula to get contact number
=VLOOKUP(H2,A:E,5,0)
Note -
1. The reference value which we are using to look up the data should be always in the left hand side of the required output data. in above example employee id is on the left side of the data.
2. The data range should start from the reference number column. In above example range is starting from (A;E) where A is employee id column.
3. The number for the output column should be counted from the start column of reference value. In example above we are counting the output value from column A, i.e. our output values are in column 4 and 5.
HlookUp stands for horizontal row look up. This formula is used to find the value in the rows horizontally.
e.g.
We have a table of students marks details, where column headers are
Student Name
English
The data is in column A, B, C, D and E respectively.
below is the formula which can be used to get the required output
=HLOOKUP(B8,$A$1:$E$5,3,0)
result = 27
There is one challenge in the above formula for which we need to take help of one more formula combining with Hlookup.
Challenge : For 5000 students how to enter the number automatically in step 3 above
Solution : use the MATCH formula to find the row number in which our required data is present
e.g. find row number for Vijay, use the below match formula
=MATCH(A9,$A$1:$A$5)
result = 3
Now combining the Match formula with Hlookup, we can get the required output without manual entry.
Our actual Hlookup formula:
=HLOOKUP(B8,$A$1:$E$5,3,0)
Formula combined with MATCH:
=HLOOKUP(B8,$A$1:$E$5,MATCH(A9,$A$1:$A$5),0)
result = 32
e.g.
We have a table of employee details, where employee id, first name, last name, email id and contact number data is there. The data is in column A, B, C, D and E respectively.
This table contains data for 10000 employees.
Problem : We need to get the email id and contact number data for 43 employees in the output.
The only input we have is employee id in column H.
Solution - Vlookup
below is the formula which can be used to get the employee details -
Get email id
=VLOOKUP(H2,A:E,4,0)
how it works ?
1. here H2 is the reference employee id for which we need to find the data
2. A:E is the table range where our data is present
3. 4 is the number of the column starting from column A, in which our data is present
4. 0 is for exact match
Similarly use the below formula to get contact number
=VLOOKUP(H2,A:E,5,0)
Note -
1. The reference value which we are using to look up the data should be always in the left hand side of the required output data. in above example employee id is on the left side of the data.
2. The data range should start from the reference number column. In above example range is starting from (A;E) where A is employee id column.
3. The number for the output column should be counted from the start column of reference value. In example above we are counting the output value from column A, i.e. our output values are in column 4 and 5.
HlookUp stands for horizontal row look up. This formula is used to find the value in the rows horizontally.
e.g.
We have a table of students marks details, where column headers are
Student Name
English
Maths
Science
History.The data is in column A, B, C, D and E respectively.
Data under Student Name column is -
Amol
Vijay
Kiran
Vidya
....
...
..
. and more
This table contains data for 5000 Students.
Problem : We need to find the marks of English and Science, only for Vijay and Vidya or for list of say, 30 students only.
The input we have is the table data in which we can use the user name and the subject name to lookup the required value.
example table:
Solution : Hlookup
below is the formula which can be used to get the required output
result = 32
Below is the screenshot of output table, this table is on cells A8:C10.
how it works ?
1. here B8 is the subject name (English) for which we need to find the data
2. $A$1:$E$5 is the table range where our input data is present
3. 3 is the number of the row starting from column header, in which our data is present
4. 0 is for exact match
Similarly use the below formula to get data for subject Science
=HLOOKUP(C8,$A$1:$E$5,3,0)result = 27
There is one challenge in the above formula for which we need to take help of one more formula combining with Hlookup.
Challenge : For 5000 students how to enter the number automatically in step 3 above
Solution : use the MATCH formula to find the row number in which our required data is present
e.g. find row number for Vijay, use the below match formula
=MATCH(A9,$A$1:$A$5)
result = 3
how it works ?
1. here A9 is the Student name (Vijay) for which we need to find the data
2. $A$1:$A$5 is the table range where our input data is present
Now combining the Match formula with Hlookup, we can get the required output without manual entry.
Our actual Hlookup formula:
=HLOOKUP(B8,$A$1:$E$5,3,0)
Formula combined with MATCH:
=HLOOKUP(B8,$A$1:$E$5,MATCH(A9,$A$1:$A$5),0)
result = 32
No comments:
Post a Comment