Article ID: 59482 - Last Review: June 22, 2007 - Revision: 1.5

Using INDEX and MATCH Against Multiple Criteria

Retired KB Content Disclaimer

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q59482

For a Microsoft Excel 2000 and later version of this article, see 214142 (http://support.microsoft.com/kb/214142/EN-US/ ) .

SUMMARY

This article includes sample data and formulas that retrieve values based on multiple criteria. These examples use multiple criteria to retrieve a part price.

Back to the top

MORE INFORMATION

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

Back to the top

Data Arranged in Columns

Assume you are using the following data:

   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

Suppose that you want to retrieve the price for part y with code 12. (The input cells are D2 and E2.) To retrieve the price for this part, 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))

The formula returns the value 8.00.

You must enter this formula as an array formula by pressing CTRL+SHIFT+ENTER (in Microsoft Excel for Windows and OS/2) or COMMAND+RETURN (in Microsoft Excel for the Macintosh).

You can then fill the formula by using the fill handle for cell F2 to retrieve the price for each part and code combination.

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against multiple criteria (more than two) because it does not require nested IF statements:

   =INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))

You must also enter this formula as an array formula. For more information about the concatenation operator, see the "Text, operators in formulas" topic in the index of "User's Guide 1."

Note that you are not limited to two conditions, for example

   =INDEX(A2:E5,(MATCH(G1&H1&I1&J1,A2:A5&B2:B5&C2:C5&D2:D5,0)),5)

entered as an array returns the text or value in column E (the fifth column in the range A2:E5) that matches all four conditions in G1, H1, I1, and J1 in the respective columns A, B, C, D.

Back to the top

Data Arranged in Rows

Assume you are using the following data:

   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

Suppose that you want to retrieve the price for part y with a code of 12, for example, with input cells B4 and B5. Type the following formula in cell B6 to retrieve the price for this part:

   =INDEX($B$3:$E$3,MATCH(B4,IF($B$2:$E$2=B5,$B$1:$E$1),0))

The formula returns the value 8.00.

You must enter this formula as an array formula by pressing CTRL+SHIFT+ENTER (in Microsoft Excel for Windows and OS/2) or COMMAND+RETURN (in Microsoft Excel for the Macintosh).

You can then fill the formula to the right by using the fill handle for cell B6 to retrieve the price for each part and code combination.

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against multiple criteria (more than two) because it does not require nested IF statements:

   =INDEX($B$3:$E$3,MATCH(B4&B5,$B$1:$E$1&$B$2:$E$2,0))

You must also enter this formula as an array formula. For more information about the concatenation operator, see "Text, operators in formulas" in the index of "User's Guide 1."

Back to the top

REFERENCES

For more information about using lookup functions, click the Index tab in Microsoft Excel 97 Help, type the following text

   lookup functions

and then double-click the selected text to go to the "About lookup and reference functions" topic.

"User's Guide 1," version 4.0, pages 132, 138

Back to the top

APPLIES TO

Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 98 for Macintosh

Back to the top

kbhowto KB59482

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top