iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To return a value only when two or more fields match, combine the tests into one lookup array. In current Excel, multiply the tests and use XLOOKUP; for older workbooks, INDEX/MATCH can use the same pattern. The example below matches a department in column A and a product in column B, then returns the corresponding value from column C.
Use XLOOKUP for a multi-criteria match
If the criteria are in G2 and H2, enter:
=XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:C100,"Not found")Each comparison creates an array of TRUE and FALSE values. In arithmetic, TRUE and FALSE act as 1 and 0. Multiplying the arrays produces 1 only where both the department and product match; XLOOKUP searches for that 1 and returns the value from the corresponding row in column C. Microsoft documents XLOOKUP’s arguments and first-match behavior, as well as Boolean arrays in calculations: XLOOKUP function and conditional calculations and Boolean arrays.
XLOOKUP uses exact matching by default. The fourth argument, "Not found", supplies a result for cases where no row satisfies both tests. Replace it with a message that suits your workbook.
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 problemsUse INDEX/MATCH when compatibility matters
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, and INDEX returns the item at that position in column C. The final 0 tells MATCH to find an exact match. For a more readable result when there is no qualifying row, wrap the formula in IFNA, for example:
=IFNA(INDEX(C2:C100,MATCH(1,(A2:A100=G2)*(B2:B100=H2),0)),"Not found")Microsoft describes INDEX and MATCH as established lookup functions and lists XLOOKUP and XMATCH as newer alternatives. See Microsoft’s lookup-function guidance and its explanation of Boolean arrays in conditional calculations.
Choose the formula your Excel version can use
| Consideration | XLOOKUP | INDEX/MATCH |
|---|---|---|
| Version support | Microsoft says XLOOKUP is not available in Excel 2016 or Excel 2019. Those editions can open workbooks containing XLOOKUP formulas made in a newer version, but users should not assume they can author or calculate them there. Microsoft Support | A pattern to consider for legacy workbooks, but test it in the actual target edition because older Excel versions may handle array-entry formulas differently. Microsoft Support |
| Missing result | Use XLOOKUP’s dedicated if_not_found argument, as in the example. |
Wrap the formula in IFNA or IFERROR to provide a message. |
| Duplicate qualifying rows | Returns the first match by default; its search-mode argument can instead search from last to first. | Returns one matching position, not every matching record. |
For XLOOKUP’s search options, including reverse search, see Microsoft’s XLOOKUP documentation. If a workbook moves between editions or platforms, verify the formula in each environment where it will be used.
Rank #2
- Used Book in Good Condition
Adapt the formula without creating range errors
- Set the ranges. Replace
A2:A100andB2:B100with the columns to test, andC2:C100with the return column. Keep all ranges the same height so each test corresponds to the same rows. - Set the criteria cells. Replace
G2andH2with the cells containing the values to match. If you plan to fill the formula across or down, add dollar signs to references that must stay fixed, such as$G$2. - Choose the missing-result behavior. Use a clear message if no row matches. Do not substitute zero unless zero is genuinely the intended result.
- Check the result against duplicates. Decide whether the first matching row is the intended one before relying on a single-result lookup.
- Test in the target Excel versions and platforms. In particular, do not assume an array formula behaves identically in every historical edition.
Use multiplication for AND; design OR logic separately
Multiplying comparison arrays means AND: every condition must be true for a row to produce 1. If a row should qualify when either condition is true, addition can represent OR logic, but the combined lookup array must be designed and checked so its matching values express the intended rule. Do not swap multiplication for addition without confirming what values the resulting array can produce.
Recommended Free Tools
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
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.
