Lookup with two criteria in Excel
Find a price by product and size, or a score by name and date. Three ways to look up on two conditions: a helper column, XLOOKUP, and INDEX with MATCH.
A lookup with two criteria finds the row where both conditions are true. In Excel 2021 and
Microsoft 365, use =XLOOKUP(1,(A2:A9=F2)*(B2:B9=G2),C2:C9). In
older Excel, join the two columns into one key and look that up.
Here is a decorating supplier's price list. The price depends on the paint and on the tin size. An order form has the paint in F2 and the size in G2. It needs the price in H2.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Paint | Size | Price | Key |
| 2 | Matt white | 1 L | 12.50 | Matt white|1 L |
| 3 | Matt white | 2.5 L | 24.00 | Matt white|2.5 L |
| 4 | Matt white | 5 L | 41.00 | Matt white|5 L |
| 5 | Gloss white | 1 L | 14.00 | Gloss white|1 L |
| 6 | Gloss white | 2.5 L | 29.50 | Gloss white|2.5 L |
| 7 | Silk grey | 1 L | 13.00 | Silk grey|1 L |
| 8 | Silk grey | 2.5 L | 26.00 | Silk grey|2.5 L |
| 9 | Silk grey | 5 L | 44.00 | Silk grey|5 L |
Column D is the helper key for the first way below. Ignore it for now. Column C shows 2 decimal places. In every example, F2 holds Silk grey and G2 holds 2.5 L. The right answer is 26.00.
Why a normal lookup gets it wrong
=VLOOKUP(F2,A2:C9,3,FALSE) gives 13.00. That is the price of the
1 litre tin. VLOOKUP searched for Silk grey, found it in row 7, and stopped.
There is no error, and 13.00 looks like a real price. VLOOKUP, XLOOKUP and MATCH each search one column. For two conditions, you have to turn them into one thing to search for.
Way 1: a helper column
Join the two columns into one key. In D2, type this and fill it down to D9:
=A2&"|"&B2 gives Matt white|1 L.
Then join F2 and G2 the same way, and look that up in column D:
=INDEX(C2:C9,MATCH(F2&"|"&G2,D2:D9,0)) gives 26.00.
With XLOOKUP it is shorter:
=XLOOKUP(F2&"|"&G2,D2:D9,C2:C9) gives 26.00 too.
The | in the middle is a separator. Without one, two different pairs can make the same key. AB and 1 make AB1. So do A and B1. Use any character that never appears in your data.
Build the key the same way on both sides. If column D uses a separator and the lookup does not,
nothing matches and you get #N/A.
VLOOKUP can use a key too, but only if the key is the first column of the table. Put the key in column A if you have to use VLOOKUP.
Good: it works in every version, and anyone can see the key. Bad: it adds a column, and new rows need the formula filled down.
Way 2: XLOOKUP with two tests
=XLOOKUP(1,(A2:A9=F2)*(B2:B9=G2),C2:C9) gives 26.00. It needs
Excel 2021 or later, or Microsoft 365. You press Enter as normal.
Each test in brackets gives TRUE or FALSE for every row. Multiplying turns TRUE into 1 and FALSE into 0. So a row gives 1 only when both tests are TRUE.
For our sheet, (A2:A9=F2)*(B2:B9=G2) is 0, 0, 0, 0, 0, 0, 1, 0.
Only row 8 is Silk grey and 2.5 L.
XLOOKUP searches that list for 1. It finds it in the 7th place and returns the 7th price.
Now try Gloss white in 5 L. The shop does not sell it, so every row gives 0. The formula gives
#N/A. To show a message instead, add a fourth argument:
=XLOOKUP(1,(A2:A9=F2)*(B2:B9=G2),C2:C9,"Not listed").
A third condition is one more test in brackets, joined with one more star. Every range must start on row 2 and end on row 9, like the others.
Way 3: INDEX and MATCH with two tests
This is the same idea, for people who already use INDEX and MATCH:
=INDEX(C2:C9,MATCH(1,(A2:A9=F2)*(B2:B9=G2),0)) gives 26.00.
MATCH finds the 1 in the list of 0s and 1s. It gives 7. INDEX returns the 7th price.
The version matters here. In Microsoft 365 and Excel 2021 you press Enter as normal. In Excel 2019 and older, you must press Ctrl+Shift+Enter. Excel then shows curly brackets around the formula.
Do not type the curly brackets yourself. If you press only Enter in older Excel, the formula does not work. You may see an error or a wrong price.
So in a file shared with older Excel, this is the risky way. One person edits the formula and presses Enter. Then it stops working.
The SUMIFS shortcut, and why to be careful
=SUMIFS(C2:C9,A2:A9,F2,B2:B9,G2) also gives 26.00. It adds the
prices of every row that matches both conditions. One row matches, so the sum is the price.
It goes wrong in two ways, and neither shows an error:
-
No match gives 0. Gloss white in 5 L gives 0, not
#N/A. A price of 0 on an order form is easy to miss. - Two matches are added. Say Silk grey 2.5 L is listed twice, at 26.00 and 27.00. SUMIFS gives 53.00. XLOOKUP gives 26.00, the first one.
It also only works when the answer is a number. Use it only after
=COUNTIFS(A2:A9,F2,B2:B9,G2) gives 1.
A score by name and date
The same formulas work with any two columns. Say column A holds names, column B holds dates, and column C holds scores. Put the name in F2 and the date in G2, and use the XLOOKUP from Way 2.
Two things stop a date from matching. G2 may hold text that only looks like a date. Or the dates in column B may have a time on them. Then a date with no time never equals them.
When the lookup gives #N/A but the row is there
Look for a space at the end of a cell. "Silk grey " with a space is not "Silk grey". With that
one space in A8, every formula here gives #N/A.
Click the cell and press F2 to see where the text ends. Clean the column with TRIM. The #N/A error article lists the other causes.
Which one to use
Use XLOOKUP with two tests, unless the file must open in Excel 2019 or older. It is one formula, with no extra column and no special keys.
For older Excel, use the helper column with INDEX and MATCH. It needs no Ctrl+Shift+Enter, so nobody can break it by pressing Enter.
Your price list may be a grid, with paints down the side and sizes across the top. Then you need a two-way lookup instead. The INDEX and MATCH article shows how.
You can practise these in the lessons below. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.