INDEX and MATCH are two powerful Excel functions used for
retrieving data from a table. The INDEX function returns a value from a
specific location in a table, while the MATCH function finds the position of a
value in a column or row.
Here's an example of how to use INDEX and MATCH together:
Suppose you have a table of sales data for different products
and regions, like this:
|
Product |
Region |
Sales |
|
A |
NORTH |
100 |
|
B |
SOUTH |
200 |
|
C |
EAST |
150 |
|
D |
WEST |
120 |
You want to retrieve the sales figure for a particular
product and region. You can use INDEX and MATCH to do this as follows:
1. In a cell, enter the following formula:
=INDEX(C2:C5,MATCH("B",A2:A5,0),MATCH("South",B1:D1,0))
This formula
retrieves the sales figure for Product B in the South region.
2. Let's break down the formula. The first argument of the
INDEX function (C2:C5) specifies the range of values from which to retrieve the
result.
3. The second argument of the INDEX function is the row
number of the value to retrieve. The MATCH function
(MATCH("B",A2:A5,0)) returns the row number of the cell that contains
the value "B" in the Product column.
4. The third argument of the INDEX function is the column
number of the value to retrieve. The MATCH function
(MATCH("South",B1:D1,0)) returns the column number of the cell that
contains the value "South" in the Region row.
5. The third argument of the MATCH function (0) specifies an
exact match.
By using INDEX and MATCH together, you can retrieve data
from a table based on specific criteria, without the need for complex nested IF
statements or VLOOKUP functions.