The information in this article applies to:
SUMMARYThis 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 ColumnsAssume you are using the following data:
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:
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:
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." Data Arranged in RowsAssume you are using the following data:
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:
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:
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." For additional information, please see the following article in the Microsoft Knowledge Base: Q77114 : 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
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 Additional query words: XL98 XL97 XL7 XL5 XL4 XL3 index-match
Keywords : xlformula |
Last Reviewed: March 17, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |