. These are the advanced Excel Functions.
. Used to extract values from other table called Lookup/Reference Table .
. Used to extract values from other table called Lookup/Reference Table .
. Located on the Lookup & Reference drop-down menu on the Formulas tab of the Ribbon as well as in the Lookup & Reference category in the Insert Function dialog box.
Vlookup:
. It means Vertical Lookup.
. Vlookup searches vertically the column of a Lookup table until the program locates a value that matches the one you are looking for.
. Vlookup searches vertically the column of a Lookup table until the program locates a value that matches the one you are looking for.
. It can be used in 2 ways: Vlookup for exact match
Vlookup for closest match
1.Vlookup for Exact Match:
. It gives result only when it finds exact value in reference.. We use False for [range_lookup]
. It returns N/A if it does not found exact match.
. Syntax: =vlookup(lookup_value,table_array,col_index_num, False)
. Example: =vlookup(E8,$M$8:$O$11,3,False)
> Here '$' symbol is used to refer the table.
> When we drag a cell down to get same result, the table doesn't change.
2.Vlookup for Closest Match:
. We use True for [range_lookup]
. It tells Excel to find the closest match for the vlookup value if it doesn't find an exact match in the reference table.
. Syntax: =vlookup(lookup_value,table_array,col_index_num,True)
. It tells Excel to find the closest match for the vlookup value if it doesn't find an exact match in the reference table.
. Syntax: =vlookup(lookup_value,table_array,col_index_num,True)
. Example: =vlookup(E8, $M$8:$O$11,3,True)
. We use False for [range_lookup]
. It returns N/A if it does not found exact match.
. Syntax: =hlookup(lookup_value,table_array,row_index_num,False)
. Example: =hlookup(E8, $N$3:$R$4,2,False)
. It tells Excel to find the closest match for the hlookup value if it doesn't find an exact match in the reference table.
Hlookup:
. It means Horizontal lookup.
. Hlookup searches horizontally the row of a Lookup table until the program locates a value that matches the one you are looking for.
. It can be used in 2 ways : Hlookup for exact match
Hlookup for closest match
1.Hlookup for Exact Match:
. It gives result only when it finds exact value in reference.. We use False for [range_lookup]
. It returns N/A if it does not found exact match.
. Syntax: =hlookup(lookup_value,table_array,row_index_num,False)
. Example: =hlookup(E8, $N$3:$R$4,2,False)
2.Hlookup for Closest Match:
. We use True for [range_lookup]. It tells Excel to find the closest match for the hlookup value if it doesn't find an exact match in the reference table.
. Syntax: =hlookup(lookup_value,table_array,row_index_num,True)
. Example: =hlookup(E8, $N$3:$R$5,3,True)
Vlookup for different Sheets:
. We can also use lookup table from different sheet and use in current sheet.
. Consider Sheet 3 contains lookup table data.
. Select all the cells click on the TABLES.
. Now give Table Name available in top left and click enter.
. This is the reference table, we can use this in any sheets.
. Consider a table in Sheet 2 requires lookup table in Sheet 3.
. Here in this example, we use Vlookup to get Department based on Dept id.
. In the formula, write the table name instead of table_array.
. In C2, =VLOOKUP(A2,DeptTable,2,FALSE)
Examples for Vlookup & Hlookup using Google Sheet:
. Vlookup for exact match showing remarks from reference table:
. Here, table array is taken i.e. I3:K7 for formula.
. We can also define a range for reference table instead of table array.
. Vlookup for closest match showing remarks from reference table:
. Here, reference table is defined as "Remarks" and used as table_array.
. To define range, Select cells > right click > Define range.
. Hlookup for closest match showing remarks from reference table:
. Hlookup for exact match showing remarks from reference table:
