XL: Using INDEX and MATCH Against Multiple Criteria

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.0, 5.0

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.

MORE INFORMATION

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

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

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

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

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

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

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

For additional information, please see the following article in the Microsoft Knowledge Base:

   Article-ID: Q77114
   TITLE     : Performing a Lookup with Unsorted Data in Excel

REFERENCES

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

   lookup functions

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