Combining INDEX and MATCH

 

Combining INDEX and MATCH

By combining INDEX with MATCH, we can use MATCH to find the row or column number dynamically, making INDEX highly flexible for lookups.

• Syntax: INDEX(range, MATCH(lookup_value, lookup_array, 0), column_number)

Example Scenario

Suppose you have a table of products and their prices in a range A2

. Column A contains product names, and Column B contains prices. You want to look up the price of a specific product.

Product	Price
Product A	$20
Product B	$30
Product C	$25
Product D	$35

Step-by-Step Formula Breakdown

1. Identify the Range: Select the data range (e.g., B2
   for prices).
2. Match the Product Name: Use MATCH to find the row number for the product in column A.
   • =MATCH("Product C", A2:A10, 0) returns the row position of "Product C".
3. Combine with INDEX: Nest the MATCH function inside the INDEX function.
   • =INDEX(B2:B10, MATCH("Product C", A2:A10, 0))
   • This formula returns the price of "Product C" from column B.

---

Why Use INDEX and MATCH Over VLOOKUP?

• Flexibility: INDEX and MATCH allow you to look up values to the left of the lookup column, something VLOOKUP can’t do.
• Dynamic Ranges: If you add or remove columns, the INDEX and MATCH formula adapts more easily.
• Performance: INDEX and MATCH is generally faster than VLOOKUP in large datasets.

---

Real-World Examples

Example 1: Lookup with Multiple Criteria

Using INDEX and MATCH, you can look up values based on multiple criteria by combining MATCH with an ARRAYFORMULA.

Example 2: Horizontal Lookups

If your data is organized horizontally, INDEX and MATCH can perform horizontal lookups by adjusting the row and column parameters.