Tips & Tricks

Advanced Excel Tricks: Vlookup & Hlookup

VLOOKUP/ VERTICAL LOOKUP

In lay man terms, a function in advanced Excel used when there is surplus of data and you need to find a specific record from that datasheet in a topmost column.

 Syntax:- =Vlookup(lookup_value, table_array, col_index_num,[range_lookup])

Let us understand Vlookup through an example which involves easy steps. As we are not available with huge datasheet, so I have taken a small datasheet to illustrate you an example Involve easy tips and tricks by which u can learn easily.

Excel 2010 trick 2013 vlookup

Example 1:

In the right corner there is my datasheet involving Enrolment Number of employees, their name and designation on which they are employed in a company.

*click on images to get clear look

# Case 1– The objective is to fetch designation of Enrol no. given in the left most corner to find at which position they are employed in a company from huge datasheet. It’s not possible to see about each and every employee and match their enroll no. , so here the trick of vlook applies.

#Solution

  1. In required cell we insert the function of vlookup
=VLOOKUP(B4,$J$4:$L$9,3,)

excel vlookup hlookup syntaxAlso read: Make your computer talk

excel 2010 trick

Explanation: vlookup looks for the enrol number 35489 in the datasheet of the range $J$4:$L$9 and returns the value from the required column which is designation column (index number is 3). The False is set for the fourth argument to return exact match.

 

  1. Drag the vlookup function from cell C4 to C9

excel trick vlookup

Explanation: You can manually write formula for each cell or just drag. When you drag the reference of the datasheet doesn’t change ( $J$4:$L$9) but the relative reference (B4) of the cell changes to B5, B6, B7, B8, B9.

Note: In the formula to prefix “$” is important so that the range of the datasheet remains constant. If misplaced that may or may not result in #N/A# error.

Read more: Secret tricks of Google

HLOOKUP/ HORIZONTAL LOOKUP

Hlookup or horizontal lookup is similar to vlook as we discussed above, the only difference is that the specific value is searched and function is performed in topmost row instead of a column.

Syntax:- =Hlookup(lookup_value, table_array, row_index_num,[range_lookup])

Let us understand Hlookup through an example which involves easy steps. As we are not available with huge datasheet, so I have taken a small datasheet to illustrate you an example Involve easy tips and tricks by which u can learn easily.

Example 2:

vlookup hlookup excel trick 2010

In the right corner datasheet is given below include enrol no., name and designation of employees working in a company.

#Case2- The objective is to fetch designation of enroll no. i.e. 65401 and 26549 using Hlookup. So we will try to apply hlookup to get the results.

#Solution

  1. In required cell we insert the function of Hlookup
 =HLOOKUP(D7,$G$3:$N$5,3,FALSE)

hlookup vlookup excel tricks 2010
Explanation

As we need to find 65401 first so our lookup_value would be 65401 only. The table_array is the whole table here we give the reference of our table i.e., $G$3:$N$5. The row_index_num is the row from which the value need to be fetched. In this case the value need to be fetch from 3rd column. Range_lookup will be False here because we are required to find the exact match from the datasheet.

excel trick

The result for – 65401 is manager

  1. Drag the Hlookup function from cell D8 to E8

excel trick hlookup

Explanation: You can manually write formula for each cell or perform drag. When you drag the reference of the datasheet doesn’t change ($G$3:$N$5) but the relative reference (D8) of the cell changes to E8.

Note: In the formula to prefix “$” is important so that the range of the datasheet remains constant. If misplaced that may or may not result in #N/A# error.

 

Leave a Reply

Your email address will not be published. Required fields are marked *