Hoofdstuk 17 · 17.4
Opzoeken op twee voorwaarden tegelijk
VERT.ZOEKEN en X.ZOEKEN zoeken op één waarde (zie VERT.ZOEKEN geeft #N/A: zo los je het op en X.ZOEKEN: opzoeken naar links en met een eigen melding). In de praktijk zoek je vaak op twee dingen tegelijk: de prijs van een boeket in maat L, het tarief van een klant in een bepaald jaar, de voorraad van een product in een bepaalde kleur. Daar zijn drie goede manieren voor, die allemaal met gewoon Enter werken. Een vierde manier die je op internet veel ziet, werkt in Euro-Office niet zonder meer.
Als voorbeeld nemen we de prijslijst van Bloemisterij Het Zonnetje. Hetzelfde product staat er meerdere keren in, in verschillende maten:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Maat | Prijs |
| 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 |
Het gezochte product staat in F2 (Boeket), de maat in G2 (L). We zoeken 34,50.
Manier 1: X.ZOEKEN met samengevoegde waarden
=X.ZOEKEN(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"niet gevonden")
Het &-teken plakt tekst aan elkaar. F2&"|"&G2 wordt "Boeket|L". A2:A7&"|"&B2:B7 maakt van elke rij in de lijst ook zo'n combinatie ("Boeket|S", "Boeket|M" enzovoort), en X.ZOEKEN zoekt daarin. Het streepje | ertussen voorkomt dat twee verschillende combinaties toevallig dezelfde tekst opleveren. Bestaat de combinatie niet, bijvoorbeeld een rouwstuk in maat S, dan krijg je "niet gevonden".

Kijk je na het typen in de formulebalk, dan staat er een @ voor A2:A7 en B2:B7. Dat zet Euro-Office er zelf neer; de uitkomst klopt gewoon.
Manier 2: FILTER voor alle treffers
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"geen")
FILTER geeft niet alleen de eerste, maar álle rijen die aan beide voorwaarden voldoen. Komt een combinatie twee keer voor, dan zie je beide prijzen onder elkaar. Zorg dus dat er onder de formule ruimte is (zie Lijsten die zichzelf bijwerken met FILTER, UNIEK en SORTEREN). Voor "of" in plaats van "en" gebruik je een plusteken: (A2:A7="Boeket")+(A2:A7="Rouwstuk").
Manier 3: SOMMEN.ALS als de combinatie uniek is
=SOMMEN.ALS(C2:C7;A2:A7;F2;B2:B7;G2)
Klinkt vreemd, want je wilt niets optellen. Maar komt elke combinatie maar één keer voor, dan is de som gewoon die ene prijs. Het voordeel: SOMMEN.ALS is eenvoudig, snel en kent iedere Excel. Het nadeel: bestaat de combinatie niet, dan krijg je 0 (€ 0,00) in plaats van een melding. En staat de combinatie er per ongeluk twee keer in, dan krijg je beide prijzen opgeteld.
Wat je op internet vindt: INDEX en VERGELIJKEN met een vermenigvuldiging
Veel Excel-sites geven =INDEX(C2:C7;VERGELIJKEN(1;(A2:A7=F2)*(B2:B7=G2);0)). In Euro-Office geeft die formule met gewoon Enter #N/A, ook als de combinatie bestaat. Sluit je hem af met Ctrl+Shift+Enter, dan krijgt de formule accolades {…} en werkt hij wel. Dat vergeet je makkelijk, dus gebruik liever X.ZOEKEN of FILTER hierboven.
Zoeken op rij én kolom
Staat je prijslijst als tabel met de producten onder elkaar en de maten naast elkaar (een matrix), dan gebruik je INDEX met twee keer VERGELIJKEN. Met de producten in A11:A13, de maten S, M en L in B10:D10 en de prijzen in B11:D13:
=INDEX(B11:D13;VERGELIJKEN(F2;A11:A13;0);VERGELIJKEN(G2;B10:D10;0))
De eerste VERGELIJKEN zoekt de rij van het product, de tweede de kolom van de maat. INDEX pakt de cel op het kruispunt.
Welke kies je?
| Situatie | Beste keuze |
|---|---|
| Eén antwoord, met een nette melding als het niet bestaat | X.ZOEKEN met samengevoegde waarden |
| Het bestand moet ook in een oude Excel werken | SOMMEN.ALS |
| Er kunnen meerdere treffers zijn | FILTER |
| Prijstabel met maten als kolomkoppen | INDEX met twee keer VERGELIJKEN |
Valkuilen
- #N/A terwijl de combinatie er echt staat: vaak een spatie achter een waarde ("Boeket ") of een getal dat als tekst is opgeslagen. Zie VERT.ZOEKEN geeft #N/A: zo los je het op.
- Bereiken van ongelijke lengte: =X.ZOEKEN(F2;A2:A7;C2:C50) geeft #VALUE!. Laat alle bereiken in dezelfde rij beginnen en eindigen.
- Hele kolommen (A:A) in deze formules maken een groot bestand traag, omdat Euro-Office dan een miljoen rijen samenvoegt. Gebruik een vast bereik zoals A2:A500. Zie Een trage spreadsheet sneller maken.
- Een collega met Excel 2019 of ouder ziet bij X.ZOEKEN en FILTER #NAME?.
