Index Match


The INDEX MATCH formula is a combination of two separate functions in Excel and it's often used as a more flexible alternative to the VLOOKUP or HLOOKUP functions. Here's how they work:

  1. INDEX: Returns a value from a specific location in a table or range.
  2. MATCH: Returns the position of a value in a specific range.

Combined, the INDEX MATCH formula can return the value at a specified location within a range. Here's the basic syntax:

=INDEX(array, MATCH(lookup_value, lookup_array, match_type))

Here's how you'd use each component:

Here's an example of how it might be used. Let's say you have the following table:

ID Name
1 123 John
2 456 Jane
3 789 Sam

If you want to find out the name associated with ID 456, you can use the INDEX MATCH formula like this:

=INDEX(C2:C4, MATCH(456, B2:B4, 0))

This formula would return Jane because Jane is in the position (row) in the C column that matches where 456 is found in the B column.

The advantage of INDEX MATCH over functions like VLOOKUP is that it doesn't require the lookup column to be to the left of the return value column. It also handles columns getting inserted or deleted in your table without breaking, and is generally more flexible.