This article offers several examples that use the INDEX and
MATCH worksheet functions in Microsoft Excel to find a value based upon
multiple criteria.
Back to the top
The following examples use the INDEX and MATCH worksheet
functions to find a value based on multiple criteria.
Back to the top
Example 1: Data in Columns
Method 1
| 1. | Start Excel. |
| 2. | Type the following data into a new worksheet:
A1: Part B1: Code C1: Price D1: Find Part E1: Find Code
A2: x B2: 11 C2: 5.00 D2: y E2: 12
A3: x B3: 12 C3: 6.00 D3: y E3: 11
A4: y B4: 11 C4: 7.00 D4: x E4: 12
A5: y B5: 12 C5: 8.00 D5: x E5: 11
|
| 3. | To retrieve the price for part y with code 12 and return
the value to cell F2, type the following formula in cell F2: =INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0)) |
| 4. | Press CTRL+SHIFT+ENTER to enter the formula as an array
formula.
The formula returns the value 8.00. |
| 5. | Select cell F2, grab the fill handle, and then fill down to
cell F5 to retrieve the price for each part and code combination. |
Method 2
A second method yields the same results but uses concatenation
instead. The following sample formula may be better for matching data against
more than two criteria because it does not require nested IF statements. This
method is identical to Method 1 except that you replace the formula in step 3
with the following formula:
=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))
Back to the top
Example 2: Data Arranged in Rows
Method 1
| 1. | Start Excel. |
| 2. | Type the following data into a new worksheet:
A1: Part B1: x C1: x D1: y E1: y
A2: Code B2: 11 C2: 12 D2: 11 E2: 12
A3: Price B3: 5.00 C3: 6.00 D3: 7.00 E3: 8.00
A4: Find Part B4: y C4: y D4: x E4: x
A5: Find Code B5: 12 C5: 11 D5: 12 E5: 11
|
| 3. | To retrieve the price for part y with code 12 and return
the value to cell B6, type the following formula in cell B6: =INDEX($B$3:$E$3,MATCH(B4,IF($B$2:$E$2=B5,$B$1:$E$1),0)) |
| 4. | Press CTRL+SHIFT+ENTER to enter the formula as an array
formula.
The formula returns the value 8.00. |
| 5. | Select cell B6, grab the fill handle, and then fill right
to cell E6 to retrieve the price for each part and code combination. |
Method 2
A second method yields the same results but uses concatenation
instead. The following sample formula may be better for matching data against
more than two criteria because it does not require nested IF statements. This
method is identical to Method 1 (under Example 2) except that you replace the
formula in step 3 with the following formula:
=INDEX($B$3:$E$3,MATCH(B4&B5,$B$1:$E$1&$B$2:$E$2,0))
Back to the top