Het is alweer een tijdje geleden dat we een Excel tip plaatsten op Projectsucces, maar het is er weer tijd voor. Er is namelijk een bijzonder interessante nieuwe functie toegevoegd om nog makkelijker te zoeken naar een bepaalde waarde in een tabel. Vandaar deze Excel tip: XLOOKUP (X.ZOEKEN).
Beperkingen verticaal zoeken
Voor de volledigheid neem ik nog even een stapje terug door uit te leggen wat verticaal zoeken ook alweer is en wat de beperkingen zijn.
Met verticaal zoeken, kun je een waarde ophalen die op dezelfde rij staat als je zoekwaarde. Zie dit voorbeeld met de formule =VLOOKUP(C2; B7:D9; 3; FALSE) die zich in cel D2 bevindt:
We zoeken op Test2 (C2) in de eerste kolom van de matrix B7:D9. We halen vervolgens de waarde op uit kolom 3 van die matrix, mits deze 1-op-1 matcht (de FALSE/ONWAAR in de formule). In dit geval is dat 456 (die dus in D2 is gevuld). Een super handige functie die veel mensen kennen die veel met lijstwerk geconfronteerd worden.
Er is alleen een probleem. Je definieert maar één matrix, waarbij je zoekwaarde altijd in de eerste kolom moet staan, en de waarde die je erbij wilt zoeken kan daar alleen maar rechts van staan. Als je in dit geval dus had willen zoeken op 456, om vervolgens de waarde “Test2” op te halen, dan kan dat niet. Je kunt niet “-2” ofzo in de verticaal-zoeken-formule kwijt.
Dit is bijzonder hinderlijk, want als je veel met lijsten werkt, dan zal het herkenbaar zijn dat je altijd je sheets aan het verbouwen bent met extra kolommen aan het begin om uiteindelijk goed overweg te kunnen met verticaal zoeken. Grote kans dat je wilt zoeken in een sheet die uit één of ander systeem rolt, en dat betekent weer dat je die altijd moet verbouwen voor je er mee aan de slag kunt. Of je moet de data kopiëren en plakken in een sheet die je klaar hebt staan met de voorloopkolommen voorbereid. Hoe dan ook: niet handig.
Er is nog een andere manier om te zoeken, namelijk gebruik van de INDEX en MATCH formules, maar die wordt door de meeste mensen te ingewikkeld gevonden.
XLOOKUP (X.ZOEKEN) to the rescue Excel tip: XLOOKUP (X.ZOEKEN)
Aan bovenstaande ellende komt nu een eind met de komst van de XLOOKUP functie! Deze functie werkt namelijk niet alleen rechtsaf, maar ook linksaf, afhankelijk van waar je wat vandaan wilt halen! Meteen maar even een voorbeeld met de formule =XLOOKUP(C11; D16:D18; B16:B18) die zich in cel D11 bevindt:
Je ziet hier dat we zoeken op het getal 456 (C11) in het bereik D16:D18. Het mooie is dat we kunnen zeggen dat de waarde die we willen hebben moet komen uit een bereik dat zowel links als rechts van de zoekwaarden kan staan. In dit geval kiezen we B16:B18. Dit levert netjes de waarde 0,3 op.
Maar: het bereik kan ook ergens anders staan!
Het vorige voorbeeld maakt nog even gebruik van de verticaal zoeken gedachte, maar nu komt ‘ie: het bereik waar je de waarde uit kan halen, kan ook ergens anders staan! Zie dit voorbeeld met de formule =XLOOKUP(C11; D16:D18; B20:B22) die zich in D11 bevindt:
We zoeken nog steeds op getal 456, deze wordt nog steeds gezocht in bereik D16:D18, en deze wordt nog steeds gevonden in rij 2 van dat bereik. Maar: nu zoeken we de bijpassende waarde in het bereik B20:B22 en we vinden netjes de waarde uit rij 2 van dat bereik, namelijk 0,90!
Bovenstaande verbeteringen zijn een enorme verbetering op de beperkingen van verticaal zoeken en zijn zó makkelijk in het gebruik, dat dit echt een enorme stap voorwaarts is in het gebruik van Excel en met het werken van lijstwerk.
Er is meer: weg met #N/A’s! Excel tip: XLOOKUP (X.ZOEKEN)
Ik heb in de bovenstaande voorbeelden de meest basale XLOOKUP (X.ZOEKEN) formule gebruikt die je kan gebruiken. Maar er is meer! Met verticaal zoeken kom je in de omstandigheid dat als je zoekwaarde niet bestaat, je een #N/A melding te zien krijgt. Om daarmee om te gaan, zal je altijd iets moeten doen met een ALS.FOUT (IFERROR) formule of iets dergelijks. Ook dat is met XLOOKUP verleden tijd, want er is een parameter aanwezig voor de “waarde indien niet gevonden”. Een voorbeeldje:
Het enige verschil wat gewijzigd is in de formule, is de toevoeging van een 4e parameter die ik heb gevuld met de tekst “Not found”. Je ziet dat we nu zoeken op 450. Deze waarde komt niet voor in het zoekbereik D16:D18. Dit zou normaal gesproken een fout op hebben geleverd, maar nu kunnen we hiervoor dus een foutwaarde opgeven. In dit voorbeeld een tekstje, maar dit had natuurlijk ook 0 kunnen bijvoorbeeld.
Geen exacte matches: second best Excel tip: XLOOKUP (X.ZOEKEN)
Een fout zoals hierboven kun je niet alleen opvangen met een waarde die gebruikt kan worden als er niets wordt gevonden: je kunt ook zoeken naar een alternatieve waarde om een fout of “niet gevonden” te voorkomen. Standaard zoekt Excel altijd naar een exacte match (de optie “0”), maar met een 5e parameter in de formule kun je zoeken op de dichtstbijzijnde kleinere waarde (“-1”), de dichtstbijzijnde grotere waarde (“1”), of je kan zoeken met wildcards (* of ?, optie “2”).
Weer een voorbeeld: we zoeken op de tekst Test22, maar gebruiken nu ook de 5e parameter: de 1. We zoeken dus naar de eerstvolgende grotere waarde van Test22 en dat is in dit geval Test23. Deze wordt gevonden in rij 2, en dus haalt de XLOOKUP de waarde 0,90 op uit het doelbereik:
Hadden we in dit voorbeeld gekozen voor de zoekoptie -1 in plaats van 1, dan had Excel gezocht naar de eerstvolgende kleinere waarde. Dat is Test11, en het resultaat was dan 0,70 geweest.
Zoals gezegd kan je ook zoeken met wildcards (optie 2). Dit wil zeggen dat je kan zoeken op een tekst met een variabele inhoud. Zoeken op “Test?A” betekent hierbij dat op het vraagteken een willekeurig karakter kan staan (zoals “Test1A” of “TestxA”). Zoeken op “Test*A” betekent dat je zoekt naar iets waarbij op de asterisk van alles kan staan van verschillende lengtes, zolang het maar begint met “Test” en eindigt op “A”.
Conclusie
De nieuwe XLOOKUP functie is zó flexibel, dat je je uberhaupt nog even kunt afvragen of je VLOOKUP nog wel blijft gebruiken. Het is een enorme stap die voor veel Excellers enorm veel toevoegt en het werk echt stukken makkelijker maakt.
