How to Use Excel INDEX and MATCH Functions

The INDEX function in Excel is used to get the value of a cell from a table when you specify the row and column numbers. In contrast the MATCH function provides the position of a cell in an array that has a certain value. Our article will guide you on how to use the INDEX and MATCH in formulas on Excel.

How do I use INDEX and MATCH formulas in Excel?

1. Understanding the functions

• INDEX: Returns the value of a cell in a table based on the row and column number.
  • Syntax: =INDEX(array, row_num, [column_num])
• MATCH: Returns the relative position of an item in an array that matches a specified value.
  • Syntax: =MATCH(lookup_value, lookup_array, [match_type])

2. Basic example

Let’s say you have a table of sales data:

Product	Sales
A	100
B	200
C	300

You want to find the sales for Product B.

3. Using MATCH to find the row number

• Formula: =MATCH("B", A2:A4, 0)
  • lookup_value: “B”
  • lookup_array: A2:A4 (the range where you’re looking for B)
  • match_type: 0 (exact match)

This formula returns 2 because B is in the second row of the range A2:A4.

4. Using INDEX to find the sales value

• Formula: =INDEX(B2:B4, 2)
  • array: B2:B4 (the range where the sales data is)
  • row_num: 2 (the row number found by the MATCH function)

This formula returns 200, which is the sales value for Product B.

5. Combining INDEX and MATCH functions

We are addressing the INDEX and MATCH formulas because you can combine them conveniently.

• Formula: =INDEX(B2:B4, MATCH("B", A2:A4, 0))

This combined formula will return 200, the sales value for Product B, in one step.

If you have a more complex table and want to look up values based on multiple criteria, you can use a combination of INDEX and MATCH with nested functions.

Product	Region	Sales
A	North	100
B	South	200
C	East	300
A	South	150

To find the sales for Product A in the South region:

• Formula: =INDEX(C2:C5, MATCH(1, (A2:A5="A")*(B2:B5="South"), 0))
  • array: C2:C5 (the range where the sales data is)
  • MATCH(1, (A2:A5=”A”)*(B2:B5=”South”), 0): This part creates an array of 1s and 0s where both conditions are met and finds the position of the first 1.

This formula returns 150, the sales value for Product A in the South region.

Additional tips for using INDEX and MATCH

• Ensure your data ranges are correct and consistent.
• Use absolute references (e.g., $A$2:$A$5) if you plan to copy the formula to other cells.
• Use 0 for exact matches in the MATCH function to avoid errors.

By following these steps, you can effectively use INDEX and MATCH functions in formulas and even combine them to perform powerful lookups in Excel. 

If you want to learn more functions and formulas in Excel, we also have guides on how to count distinct values in a table and how to add bold text in formulas.

For any questions or suggestions, feel free to use the comments section below.