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 Lists that update themselves 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.
