Chapter 17 · 17.4
Looking up on two conditions at once
VLOOKUP and XLOOKUP look up a single value (see VLOOKUP returns #N/A: here's how to fix it and XLOOKUP: looking up to the left and with your own message). In practice you often need to look up two things at once: the price of a bouquet in size L, a customer's rate in a particular year, the stock of a product in a particular colour. There are three good ways to do this, and all of them work with a plain Enter. A fourth method you often see online doesn't simply work in Euro-Office.
As an example, we'll use the price list of Bloemisterij Het Zonnetje. The same product appears several times, in different sizes:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Size | Price |
| 2 | Boeket | S | 17.50 |
| 3 | Boeket | M | 24.50 |
| 4 | Boeket | L | 34.50 |
| 5 | Rouwstuk | M | 65.00 |
| 6 | Rouwstuk | L | 89.00 |
| 7 | Bruidsboeket | M | 79.50 |
The product you're looking for is in F2 (Boeket), the size in G2 (L). We're looking for 34.50.
Method 1: XLOOKUP with combined values
=XLOOKUP(F2&"|"&G2,A2:A7&"|"&B2:B7,C2:C7,"not found")
The & sign joins text together. F2&"|"&G2 becomes "Boeket|L". A2:A7&"|"&B2:B7 turns every row in the list into a similar combination ("Boeket|S", "Boeket|M" and so on), and XLOOKUP searches through those. The | separator stops two different combinations from accidentally producing the same text. If the combination doesn't exist, for example a Rouwstuk in size S, you get "not found".

If you look at the formula bar after typing, you'll see an @ in front of A2:A7 and B2:B7. Euro-Office adds this itself; the result is still correct.
Method 2: FILTER for all matches
=FILTER(C2:C7,(A2:A7=F2)*(B2:B7=G2),"none")
FILTER returns not just the first row but all rows that meet both conditions. If a combination appears twice, you'll see both prices one below the other. So make sure there's space below the formula (see Self-updating lists with FILTER, UNIQUE and SORT). For "or" instead of "and", use a plus sign: (A2:A7="Boeket")+(A2:A7="Rouwstuk").
Method 3: SUMIFS if the combination is unique
=SUMIFS(C2:C7,A2:A7,F2,B2:B7,G2)
This sounds odd, because you don't want to add anything up. But if each combination appears only once, the sum is simply that one price. The advantage: SUMIFS is simple, fast and works in every version of Excel. The disadvantage: if the combination doesn't exist, you get 0 (€0.00) instead of a message. And if the combination accidentally appears twice, you get both prices added together.
What you'll find online: INDEX and MATCH with multiplication
Many Excel sites suggest =INDEX(C2:C7,MATCH(1,(A2:A7=F2)*(B2:B7=G2),0)). In Euro-Office this formula returns #N/A with a plain Enter, even if the combination exists. If you finish it with Ctrl+Shift+Enter, the formula gets curly brackets {…} and does work. That's easy to forget, so it's better to use XLOOKUP or FILTER above.
Looking up by row and column
If your price list is a table with the products listed down the side and the sizes across the top (a matrix), use INDEX with two MATCH functions. With the products in A11:A13, the sizes S, M and L in B10:D10 and the prices in B11:D13:
=INDEX(B11:D13,MATCH(F2,A11:A13,0),MATCH(G2,B10:D10,0))
The first MATCH finds the product's row, the second the size's column. INDEX takes the cell where they cross.
Which one should you choose?
| Situation | Best choice |
|---|---|
| One answer, with a clear message if it doesn't exist | XLOOKUP with combined values |
| The file also has to work in an older version of Excel | SUMIFS |
| There may be several matches | FILTER |
| Price table with sizes as column headers | INDEX with two MATCH functions |
Pitfalls
- #N/A even though the combination really is there: often a space after a value ("Boeket ") or a number stored as text. See VLOOKUP returns #N/A: here's how to fix it.
- Ranges of unequal length: =XLOOKUP(F2,A2:A7,C2:C50) returns #VALUE!. Make all ranges start and end in the same row.
- Entire columns (A:A) in these formulas make a large file slow, because Euro-Office then combines a million rows. Use a fixed range such as A2:A500. See Speed up a slow spreadsheet.
- A colleague using Excel 2019 or older will see #NAME? with XLOOKUP and FILTER.
