Excel Lookups and Index Match

Let’s dive into lookups and the powerful INDEX MATCH combination in Excel:

1. VLOOKUP and HLOOKUP:
   • VLOOKUP: Searches for a value in the leftmost column of a table and returns a corresponding value from a specified column.
   • HLOOKUP: Similar to VLOOKUP, but searches in the top row of a table and retrieves a value from a specified row.
2. INDEX MATCH:
   • INDEX: Retrieves a value from a specific cell in a table by specifying the row and column numbers.
   • MATCH: Locates the position of a value within a range and returns its relative position (row or column).
3. Why INDEX MATCH?:
   • Unlike VLOOKUP, INDEX MATCH is more flexible:
     • It works in any direction (left to right or top to bottom).
     • You can search for values based on multiple criteria.
     • It handles non-contiguous data.
   • Example: =INDEX(data_range, MATCH(lookup_value, lookup_range, 0))