## What You'll Learn / Key Take-Aways

- How INDEX and MATCH work together to build a two-dimensional lookup that returns any cell inside a price matrix.

- How to nest MATCH inside INDEX for dynamic row and column selection based on user input.

- A step-by-step example using dropdowns (data validation) and exact-match MATCH (match_type = 0).

- Practical tips: when to lock ranges, how to avoid N/A errors, and why this beats VLOOKUP in many cases.

## Why combine INDEX and MATCH

INDEX returns a value from a range based on a row and, optionally, a column number. MATCH finds the position of a lookup value within a single row or column. When you nest MATCH calls inside INDEX, MATCH supplies the row and column numbers dynamically. That turns a static INDEX into a flexible lookup that can return any cell inside a rectangular array.

Definitions

- INDEX(array, row_num, [column_num])

Returns the value at the intersection of row_num and column_num inside array.

- MATCH(lookup_value, lookup_array, [match_type])

Returns the position of lookup_value within lookup_array. Use 0 for exact match.

## Anatomy of the combined formula

The pattern you will use is:

=INDEX(array, MATCH(row_lookup, row_lookup_range, 0), MATCH(column_lookup, column_lookup_range, 0))

Breakdown

- array: the full rectangle that contains all possible return values. In the transcript example this is B2:D4 (prices for multiple products and sizes).

- first MATCH: finds how many rows down to move. It looks up the product name inside the product list (vertical range).

- second MATCH: finds how many columns over to move. It looks up the size inside the header row (horizontal range).

- match_type: use 0 to force an exact match. This prevents accidental nearest-match behavior.

## Build the example step by step

1. Lay out your price matrix.

- Row 1: size headers, for example Small, Medium, Large across C2:G2.

- Column A: product names down A3:A7.

- Data: prices in the body B3:G7 or similar.

2. Add dropdowns for user input.

- Select the cell for product input (for example B10). Data > Data Validation > Allow: List. Source: the product list A3:A7.

- Select the cell for size input (for example C10). Data > Data Validation > Allow: List. Source: the header row C2:G2.

Dropdowns help avoid typing errors and ensure MATCH finds exact values.

3. Start the INDEX formula.

- In the result cell, type =INDEX(

- For array, select the full price table (for example B3:G7). If you plan to copy the formula, press F4 to lock the reference. Otherwise a relative reference may be fine for a single-cell formula.

4. Insert the first MATCH for the row number.

- After the comma, type MATCH(

- lookup_value: the product dropdown cell (B10).

- lookup_array: the vertical list of products (A3:A7).

- match_type: 0 for exact match.

- Close that MATCH and add a comma.

5. Insert the second MATCH for the column number.

- Type MATCH(

- lookup_value: the size dropdown cell (C10).

- lookup_array: the header row (C2:G2).

- match_type: 0 for exact match.

- Close MATCH and then close INDEX.

6. Test.

- Choose different product and size combinations. INDEX will use the positions returned by MATCH to return the correct price.

Example formula (adapt ranges to your sheet):

=INDEX(B3:G7, MATCH(B10, A3:A7, 0), MATCH(C10, C2:G2, 0))

If B10 is "Pants" and C10 is "Medium", MATCH returns the row and column positions inside the specified arrays. INDEX then returns the intersecting price.

## Practical notes and small refinements

- Use absolute references when you want the ranges to remain fixed. Press F4 after selecting a range to toggle $ notation.

- Always use match_type 0 for exact matching when dealing with labels, names, or categories. Omitted or 1 can give unexpected nearest matches.

- If your table headers and list ranges are not aligned with the index array, adjust the MATCH ranges so their positions align exactly with the array's row and column indexes.

- For better readability and maintenance, use named ranges (for example Prices, Products, Sizes). Then the formula becomes:

=INDEX(Prices, MATCH(ProductChoice, Products, 0), MATCH(SizeChoice, Sizes, 0))

## Additional Tips, Pitfalls to Avoid & Pro Advice

- N/A errors: If MATCH does not find a lookup_value, it returns #N/A, which will make INDEX return #N/A. Wrap the formula with IFERROR or validate inputs to avoid that.

=IFERROR(INDEX(...), "Not found")

- Orientation matters: MATCH only searches a single row or column. Make sure your MATCH ranges are 1-D and correspond to INDEX's row or column dimensions.

- VLOOKUP limitations: VLOOKUP requires the lookup column to be leftmost and can break if you insert columns. INDEX + MATCH does not have that restriction and is more robust when tables change.

- Performance: For very large tables, INDEX and MATCH are generally efficient. If you nest many volatile functions, consider evaluating performance.

- Newer Excel: XLOOKUP offers simpler syntax for many lookups. INDEX + MATCH remains valuable for two-dimensional lookups where you need both row and column positions.

## FAQ

### How do I use INDEX and MATCH to look up a value by row and column?

Use MATCH to find the row position based on a product list and another MATCH to find the column position based on a header row. Insert both MATCH calls into INDEX(array, row_match, column_match).

### Why use INDEX + MATCH instead of VLOOKUP?

INDEX + MATCH is more flexible. It does not require the lookup column to be leftmost, it handles inserted columns safely, and it can perform two-dimensional lookups by supplying both row and column indices.

### What does match_type 0 mean in MATCH?

match_type 0 forces an exact match. MATCH will return the position only if it finds an exact equal value. This is the recommended setting for labels and categories.

### How do I stop the ranges from shifting when I copy the formula?

Use absolute references by pressing F4 after selecting a range, or use named ranges. Example: $A$3:$A$7 or Prices for clarity and stability.

### How can I handle #N/A errors when MATCH does not find a value?

Wrap the full formula in IFERROR to return a friendly message or blank. Also use data validation dropdowns to prevent mistyped inputs.

### Can I use INDEX + MATCH with dropdowns for user input?

Yes. Using Data Validation to create dropdowns for the product and size inputs is exactly what makes the example user-friendly. Dropdowns reduce typing errors and guarantee exact matches.

## Summary of Key Points / Take-Home Messages

- INDEX returns a value by row and column; MATCH returns the position inside a single row or column.

- Nest MATCH inside INDEX to create dynamic row and column selection for two-dimensional lookups.

- Use match_type 0 for exact matches and lock ranges with $ or named ranges for stable formulas.

- Use data validation to avoid typing errors and wrap formulas with IFERROR to handle missing matches gracefully.

With this pattern you can build a small price checker, a cross-tab lookup, or any tool that needs to pick a single value from a grid based on two selectors. Start simple, test a few combinations, and then add named ranges or error handling as needed.