Below are all three methods with the same sample data, so you can compare them. After that come the traps that cause #N/A, how to get the last match or every match, and how to match across several columns.
TL;DR
- AND logic (best default): =XLOOKUP(1,(range1=value1)*(range2=value2),return_range). Multiplying the tests gives a 1 only where every condition is true.
- Concatenation: =XLOOKUP(value1&"|"&value2,range1&"|"&range2,return_range). It is easier to read for two conditions, but less flexible for complex rules.
- OR logic: add the tests instead of multiplying them, and wrap the sum in >0.
- Only the first match comes back. Use search_mode -1 for the last match, or FILTER when you need all of them.
- Version matters: XLOOKUP needs Excel 365 or Excel 2021. Older versions can use INDEX/MATCH.
Table of Contents
The sample data used in every example
Say you track product prices by region. The data sits in A1:D11, with headers in row 1:
| Row | A: Region | B: Product | C: Rep | D: Price |
| 2 | East | Laptop | Dana | 950 |
| 3 | East | Monitor | Dana | 210 |
| 4 | West | Laptop | Priya | 980 |
| 5 | West | Monitor | Priya | 225 |
| 6 | East | Dock | Marcus | 140 |
| 7 | West | Dock | Marcus | 150 |
| 8 | North | Laptop | Lena | 940 |
| 9 | North | Monitor | Lena | 205 |
| 10 | East | Laptop | Marcus | 960 |
| 11 | West | Laptop | Dana | 990 |
You type the region in G2 and the product in G3. You want the price. A plain
XLOOKUP(G2,A2:A11,D2:D11) stops at the first East row, which is the wrong answer for "East laptop" if you meant a specific product. You need both conditions at once.
Can you use XLOOKUP with two conditions?
Yes. XLOOKUP searches one lookup array for one lookup value, and the syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]), as described on Microsoft's XLOOKUP page. The trick is that the lookup array does not have to be a column from your sheet. It can be an array that Excel calculates on the fly from several columns. How-To Geek describes the two ways to do it: Boolean logic, or concatenating the lookup values and the arrays.
The same idea scales to three, four or more conditions. Each extra condition is one more test multiplied in, or one more column joined on.
Method 1: Boolean logic (multiply the tests)
This is the method to learn first.
=XLOOKUP(1,(A2:A11=G2)*(B2:B11=G3),D2:D11,"No match")
Here is what Excel does:
- (A2:A11=G2) produces a column of TRUE and FALSE: is each row in the East region?
- (B2:B11=G3) does the same for the product.
- Multiplying turns TRUE/FALSE into 1/0 and combines them. A row gets 1 only when both tests are TRUE. Every other row gets 0.
- XLOOKUP looks for the number 1 in that temporary column and returns the matching cell from D2:D11.
With East and Laptop typed in G2 and G3, the temporary array is 1 for rows 2 and 10 and 0 for everything else. The first 1 is row 2, so the result is 950. Row 10 (also East Laptop, 960) is ignored, which is an important detail covered further down.
The pattern extends naturally. Exceljet shows a three-criteria version, =XLOOKUP(1,(B5:B15=H5)(C5:C15=H6)(D5:D15=H7),E5:E15), and recommends Boolean logic over concatenation when the criteria get complex, because it is more flexible. You can swap = for >, < or <> in any test, which concatenation cannot do. For example, (D2:D11>200) limits matches to prices above 200.
The multiplication is AND logic. ExcelChamps states the rule plainly: multiplied conditions give AND, added conditions give OR.
Method 2: Concatenation (join the columns into one key)
If you think of a lookup as "find this one key," concatenation feels natural.
=XLOOKUP(G2&"|"&G3,A2:A11&"|"&B2:B11,D2:D11,"No match")
The lookup value becomes East|Laptop. The lookup array becomes a calculated column of East|Laptop, East|Monitor, West|Laptop and so on. XLOOKUP finds the first exact match and returns its price.
The | in the middle is not decoration. ExcelChamps recommends a delimiter when building keys, to reduce collisions between combined values. Without one, a region "AB" with product "C" and a region "A" with product "BC" both become ABC. Choose a character that never appears in your data.
Concatenation has two limits. Everything is converted to text, so a number stored as 100 in one place and text "100" in another will not match the way you expect. And you can only test for equality. When you need "greater than" or "not equal to," use Boolean logic.
Which one should you use?
| Boolean (multiply) | Concatenation (&) |
| Formula length with 2 conditions | Slightly longer | Shorter |
| Supports >, <, <> tests | Yes | No |
| Easier to explain to a colleague | Takes a minute | Immediate |
| Works for OR logic | Yes (add instead of multiply) | Not directly |
| Risk | Forgetting parentheses | Key collisions, number/text mismatches |
If your team is new to array formulas, start with concatenation for two conditions. Switch to Boolean logic the first time you need a comparison or an OR.
Method 3: OR logic (either condition is enough)
Sometimes you want the first row that satisfies at least one condition. Add the tests instead of multiplying:
=XLOOKUP(TRUE,(A2:A11=G2)+(B2:B11=G3)>0,D2:D11,"No match")
How-To Geek notes that adding the Boolean tests returns the first result matching at least one criterion. The >0 matters. A row that meets both conditions would add up to 2, not 1, so looking for a plain 1 would skip it. Converting the sum to TRUE/FALSE with >0 and looking up TRUE avoids that.
A practical use: find the first row where the rep is Dana or the price is above 980. Each additional OR is one more +(test) inside the parentheses before >0.
Match across multiple columns
"Multiple columns" means two different things, and the formulas differ.
You want to return several columns. Make the return array wider.
=XLOOKUP(1,(A2:A11=G2)*(B2:B11=G3),C2:D11) returns both Rep and Price for the matching row, spilling into adjacent cells in current Excel versions. This works because XLOOKUP returns the whole row or column slice that lines up with the match.
You want to search several columns for one value. Say a name could appear in the Rep column or a Backup column. Use the OR pattern:
=XLOOKUP(TRUE,(C2:C11=G5)+(E2:E11=G5)>0,A2:A11). It checks both columns and returns the first row where either holds the name.
You want a different condition per column. That is the AND pattern, one test per column, each against its own input cell. Each lookup array must have the same number of rows as the return array, or Excel returns an error.
Does XLOOKUP only return the first match?
Yes. Microsoft's documentation says XLOOKUP returns the item corresponding to the first match it finds. ExcelChamps repeats the warning for the multiple-criteria case: if several rows qualify, you still get only the first.
That is a quiet source of wrong numbers. In our sample, East plus Laptop matches rows 2 and 10. The formula returns 950 and says nothing about 960. Before trusting a result, count the matches with =SUM((A2:A11=G2)*(B2:B11=G3)). If it is more than 1, decide which row you actually want.
Last match. Set
search_mode to -1 to search from the bottom, per Microsoft's documentation. You must fill the skipped arguments with commas:
=XLOOKUP(1,(A2:A11=G2)*(B2:B11=G3),D2:D11,"No match",0,-1)
Now East plus Laptop returns 960 from row 10. If your data is in date order, that is a quick way to get the most recent entry.
All matches. XLOOKUP cannot list them. ExcelChamps recommends FILTER when you need every match:
=FILTER(D2:D11,(A2:A11=G2)*(B2:B11=G3),"No match")
The multiplied-condition logic is identical, so everything above transfers directly. You can also combine them: use FILTER to remove the rows you do not want, then XLOOKUP on the result to pull the first or last.
Why your formula returns #N/A or the wrong value
Work through this list in order.
- Your Excel version. The multiple-criteria methods need XLOOKUP, which ExcelChamps says means Excel 365 or Excel 2021. In older versions, the formula shows #NAME?. Use INDEX/MATCH instead (see below).
- No match exists. XLOOKUP returns #N/A when nothing matches unless you supply the if_not_found argument, as Microsoft documents. Add "No match" as the fourth argument so a blank result is intentional, not a mystery.
- Match mode. Exact match (match_mode 0) is the default, so you do not need to set it. If you changed it, or you use a binary search_mode, Microsoft notes the lookup array must be sorted correctly. Boolean lookups of 1 should stay unsorted and exact.
- Hidden spaces and text-versus-number. "East " with a trailing space is not "East". Wrap a source range in TRIM() or clean the data. Numbers stored as text fail the same way.
- Mismatched range sizes. A2:A11 tested against B2:B12 breaks the array. Keep every range the same height.
- Missing parentheses. Each test needs its own pair: (A2:A11=G2)*(B2:B11=G3). Without them, operator order changes the result.
- Looking up 1 versus TRUE. Multiplying produces numbers, so look up 1. Adding with >0 produces TRUE/FALSE, so look up TRUE.
A fast way to debug: select the test portion of the formula in the formula bar and press F9 to see the array of 1s and 0s. If there is no 1 where you expect one, the problem is in a test, not in XLOOKUP.
What if you cannot use XLOOKUP?
Older workbooks and shared files often run on versions without XLOOKUP. The classic fallback is INDEX/MATCH with the same Boolean array: =INDEX(D2:D11,MATCH(1,(A2:A11=G2)*(B2:B11=G3),0)). In versions before dynamic arrays, it must be entered with Ctrl+Shift+Enter. Our INDEX MATCH guide walks through how the pair works, and the VLOOKUP with multiple criteria exercise shows the helper-column approach, where you add a joined key column to the table and use a standard VLOOKUP on it.
If you only need a total or count under several conditions, not a lookup, skip all of this. SUMIFS and COUNTIFS are built for conditions and are simpler.
Our perspective: why we teach the Boolean version first
Most people who search for this are not stuck on syntax. They are stuck on the idea that a lookup is "find X in a column." The moment you see that Excel can build its own column of 1s and 0s, many lookup and filtering problems stop looking special.
That is why we do not teach this as a formula to memorize. We build it as three small steps, the test, the multiplication and the lookup, and let people type each step and see the result right away. Passive reading rarely sticks. Typing (A2:A11=G2) yourself, seeing TRUE and FALSE appear, then multiplying and seeing 1s and 0s, is what makes the pattern reusable next month on a different sheet.
Our advice for analysts and managers building their own reports: use Boolean logic as your default, always set if_not_found, and always count your matches once. Duplicates in the data cause more wrong lookup results than any syntax mistake.
> — Thomas Houza
Practice it until it sticks
Reading a formula is not the same as being able to write one under pressure, in an interview or in a month-end deadline. Our XLOOKUP practice exercise lets you type formulas into an Excel-like grid and get instant feedback, with every mistake explained, and no account or install. For the underlying function, see the XLOOKUP function guide, and for the AND/OR thinking behind the Boolean tests, the logical functions reference. When you want a wider set of drills, the free Excel exercises cover lookups, logic and more.
FAQ
Can I do an XLOOKUP with two conditions?
Yes. Multiply two tests, as in =XLOOKUP(1,(A2:A11=G2)*(B2:B11=G3),D2:D11), or join the columns with & and look up the joined value. Both give you the first row where both conditions are true.
Which is better, VLOOKUP or XLOOKUP?
For multiple criteria, XLOOKUP is easier, because it accepts a calculated lookup array. VLOOKUP needs a helper column that joins the criteria in the table. XLOOKUP also defaults to exact match and lets you set a not-found message. VLOOKUP is still the option in versions without XLOOKUP.
Can I search for multiple things at once in Excel?
Yes, in two ways. One lookup can test several conditions through a Boolean or concatenated array. If you need many results, use FILTER, which returns every row meeting your conditions, not just the first.
Why does my formula only return one of several matching rows?
XLOOKUP returns the first match by design. Set search_mode to -1 to get the last one, or use FILTER to return all of them.
Is there a limit on how many criteria I can use?
We have not found a published limit on the number of conditions. In practice, each added test is one more *(range=value) term, so the real limit is how readable you want the formula to stay. Past four or five conditions, a helper column or a Table is easier to maintain.
Sources
Recommended
