Lookup Functions - Hlookup, Index Match
HLookup Formula
HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching.
=HLOOKUP (value, table, row_index, [range_lookup])
Index Formula
The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves values at a given location in a list or table.
=INDEX(array, row_num, column_num)
Match Formula
The MATCH function is designed for one purpose: find the numeric position of an item in a list.
=MATCH(lookup_value, lookup_array, match_type)
Index Match together
INDEX and MATCH functions gives you better control over how the row index number and column index number changes. This is often referred to as a dynamic formula. You will learn how this dynamic duo can help prevent errors and improve your INDEX formulas.
=INDEX(array, MATCH(lookup_value, lookup_array, match_type), MATCH(lookup_value, lookup_array, match_type))
Last updated