Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsTo look up a value in Excel using more than one criterion, combine the tests into one lookup array. In current Excel versions, use XLOOKUP; for older workbooks, INDEX/MATCH is a useful alternative. The formulas below return the value from column C only when both the department in column A and product in column B match the criteria in G2 and H2.
Use XLOOKUP when both conditions must match
For a table with departments in A2:A100, products in B2:B100, and return values in C2:C100, enter:
As an Amazon Associate I earn from qualifying purchases.
=XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:C100,"Not found")Each comparison creates an array of TRUE and FALSE results. In arithmetic, TRUE and FALSE behave as 1 and 0. Multiplying the arrays leaves 1 only on rows where both conditions are true; XLOOKUP searches for that 1 and returns the corresponding entry from column C. Multiplication therefore expresses AND logic. Microsoft documents XLOOKUP’s arguments and first-match behavior and Boolean arrays in conditional calculations.
XLOOKUP uses exact matching by default. Its fourth argument, "Not found", provides a message if no row meets both conditions. Replace that text with a suitable result for your workbook.
Use INDEX/MATCH for a classic alternative
The equivalent pattern is:
=INDEX(C2:C100,MATCH(1,(A2:A100=G2)*(B2:B100=H2),0))MATCH finds the position of 1 in the combined tests, with the final 0 specifying an exact match. INDEX returns the value at that position in C2:C100. Microsoft describes INDEX and MATCH lookup use and identifies XLOOKUP and XMATCH as newer alternatives.
If a missing match is expected, wrap this formula in IFNA or IFERROR to return a clear message instead of an error. For example:
=IFNA(INDEX(C2:C100,MATCH(1,(A2:A100=G2)*(B2:B100=H2),0)),"Not found")Choose based on Excel compatibility and result behavior
| Consideration | XLOOKUP | INDEX/MATCH |
|---|---|---|
| Excel version | Not available in Excel 2016 or Excel 2019, according to Microsoft Support. | A classic lookup pattern; consider it for legacy workbooks and verify it in the target Excel version. Microsoft documents it in its lookup function guidance. |
| Missing result | Use the dedicated if_not_found argument. |
Wrap the formula in IFNA or IFERROR if you want a message. |
| Duplicate qualifying rows | Returns the first match by default; its search mode can be set to search in reverse for the last match. | Returns one matching position, not every qualifying row. |
| Multiple conditions | Multiplication combines tests as AND logic. | Multiplication combines tests as AND logic. |
Excel 2016 and Excel 2019 can open workbooks containing XLOOKUP formulas created in newer versions, but users should not assume they can author or calculate those formulas there. Older editions may also handle array formulas differently, so test the INDEX/MATCH pattern in the actual version and platform where the workbook will be used.
Adapt the formula safely
- Replace
A2:A100andB2:B100with the columns containing your criteria, and replaceC2:C100with the return range. Keep all ranges the same height so each row’s tests correspond to the same return row. - Replace
G2andH2with the cells containing the criteria. If you will fill the formula across or down, use absolute references such as$G$2and$H$2where appropriate. - Keep the final
0inMATCHfor exact matching. XLOOKUP already defaults to exact match; use its optional match mode only when a different match behavior is intended. - Decide what should happen if more than one row meets the conditions. XLOOKUP returns the first qualifying row by default; INDEX/MATCH also returns a single position. If duplicates are possible, determine whether first, last, or all matches are required.
- Choose a not-found result that cannot be mistaken for valid data. In particular, do not substitute zero unless zero has the intended meaning.
- Test the formula in the Excel editions and platforms where the workbook will be opened, especially if it must support older versions.
Use OR logic only when either condition is sufficient
The formulas above require every condition to match. If either of two conditions should qualify a row, that is OR logic, not AND. Addition or another explicit condition design can represent OR, but check that the resulting lookup array identifies the intended rows, particularly if conditions can overlap.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
