Microsoft KB Archive/214142

= How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel =

Article ID: 214142

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac

-



This article was previously published under Q214142





For a Microsoft Excel 98 and earlier version of this article, see 59482.



SUMMARY
This article offers several examples that use the INDEX and MATCH worksheet functions in Microsoft Excel to find a value based upon multiple criteria.



MORE INFORMATION
The following examples use the INDEX and MATCH worksheet functions to find a value based on multiple criteria.

Method 1
 Start Excel.  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  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))

 Press CTRL+SHIFT+ENTER to enter the formula as an array formula.

The formula returns the value 8.00. Select cell F2, grab the fill handle, and then fill down to cell F5 to retrieve the price for each part and code combination.</li></ol>

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))

Method 1
<ol> Start Excel.</li>  Type the following data into a new worksheet: <pre class="fixed_text">  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 </li> 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))

</li> Press CTRL+SHIFT+ENTER to enter the formula as an array formula.

The formula returns the value 8.00.</li> Select cell B6, grab the fill handle, and then fill right to cell E6 to retrieve the price for each part and code combination.</li></ol>

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))

Additional query words: index-match Formula examples XL2003 XL2007 XL2000 XL2002 XL2001

Keywords: kbquery kbfunctions kbhowto KB214142

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.