Look Up an Instrument Class from Multiple Criteria in Excel
Question details
The user needs to identify an instrument class by comparing multiple input values against several criteria columns in a lookup table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains an instrument-class table, and the user wants a formula that returns the class only when all required criteria match the input values.
- Observed behavior
- The lookup returns Class 2 for the displayed values, but the user expects a different class and needs to verify the matching logic.
Make sure every criteria column and input cell uses the same unit, such as µm, and the same value type. Check whether the table has more than one row with the same combination of criteria, because a lookup returns the first matching row.
Use XLOOKUP to return the class for an exact multi-criteria match
Use this method when your Excel version supports XLOOKUP and each instrument class is determined by a unique combination of criteria.
The multiplied comparisons create an array of 1s and 0s. Only a row where every comparison is TRUE produces 1, allowing XLOOKUP to return the corresponding value from the Class column.
Place each matching condition in its own column, such as Criteria 1 in A2:A100, Criteria 2 in B2:B100, and Criteria 3 in C2:C100. Put the instrument class to return in D2:D100.
Enter the values to find in separate cells, for example G2 for Input 1, H2 for Input 2, and I2 for Input 3. Format these cells consistently with the table values.
In the result cell, enter =XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2),D2:D100,"Not found"). Replace the ranges with your actual criteria and class ranges.
In an empty cell, enter =SUMPRODUCT((A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2)) to count matching rows. If the result is greater than 1, XLOOKUP returns the first matching class in the table.
Use INDEX and MATCH for a multi-criteria lookup in older Excel versions
Use this alternative when XLOOKUP is unavailable or when you need a formula compatible with older Excel workbooks.
Find the Correct Instrument Class with a Multi-Criteria Formula in WPS Spreadsheet
WPS Spreadsheet lets you compare several instrument criteria in one formula and return the matching class. Use XLOOKUP for a concise modern lookup or INDEX and MATCH when working with older-compatible spreadsheets.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the instrument criteria table.
- 2. Confirm the criteria and class columns: Check that each condition has its own table column and that the class to return is stored in a separate Class column.
- 3. Add the multi-criteria XLOOKUP: Click the result cell and enter =XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2),D2:D100,"Not found"), adjusting the ranges to your sheet.
- 4. Test the result against the source row: Change the input values or filter the table columns to confirm that the returned class comes from the row where every criterion matches.
Frequently Asked Questions
Why does my multi-criteria lookup return Class 2 instead of the class I expect?
The formula returns the first row where all compared criteria are equal. Check for duplicate criteria combinations, an omitted condition, incorrect cell references, or values that use different units or data types.
How can I find out whether more than one instrument row matches my criteria?
Use =SUMPRODUCT((A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2)). A result greater than 1 means multiple rows match the three criteria and the lookup will return the first one.
Do the criteria need to be in adjacent columns for XLOOKUP?
No. Each criteria range can be in a different column, but all ranges must cover the same rows and contain the same number of cells. For example, A2:A100, C2:C100, and F2:F100 can be combined.
What should I do if the formula returns Not found even though the values look identical?
Check for text-versus-number differences, extra spaces, rounded values, and unit differences such as µm versus mm. Use TRIM for unwanted spaces and ensure both the input cells and criteria columns use compatible numeric formats.
Can I use this lookup formula in WPS Spreadsheet?
Yes. In WPS Spreadsheet, enter the XLOOKUP formula in the result cell and update the criteria and class ranges to match your worksheet. You can also use INDEX and MATCH for compatible multi-criteria lookups.
