X.ZOEKEN-functie (XLOOKUP) - Beter dan VERT.ZOEKEN

Intuïtiever, eenvoudiger en krachtiger

De X.ZOEKEN-functie (Engelse XLOOKUP) is een ondergewaardeerde functie die meerdere (oudere) functies als VERT.ZOEKEN en ZOEKEN vervangt.

X.ZOEKEN-functie voorbeeld

=X.ZOEKEN( G4; C4:C10; E4:E10)// cel H4, output: 9

In dit voorbeeld zoeken we de student EtH (cel G4, input 1 zoekwaarde) in de kolom met studenten (cellen C4:C10, input 2 zoeken-matrix).

Als we de student vinden, dan willen we het bijbehorende cijfer uit de kolom met cijfers (cellen E4:E10, input 3 matrix-retourneren).

Gebruik van X.ZOEKEN

In de het voorbeeld hierboven gebruiken we alleen de verplichte input argumenten; wat zoek je, waar zoek je, en wat wil je als output.

Er zijn echter nog meer mogelijkheden:

• verticaal en horizontaal zoeken
• Wat te doen als er geen match gevonden wordt?
• Hoe krijg ik een waarde tot, of vanaf, een bepaalde waarde? (Net als bij VERT.ZOEKEN met argument benaderen op WAAR)
• Kan je ook van onder naar boven zoeken?
• etc.

Horizontaal en verticaal zoeken

Met X.ZOEKEN kun je ook horizontaal zoeken:

=X.ZOEKEN( D2; D4:G4; D7:G7)// cel E2, output: 13

Met X.ZOEKEN kun je dus zowel de (oudere) functies VERT.ZOEKEN als HORIZ.ZOEKEN vervangen.

Optioneel input argument 4: indien-niet-gevonden

Het 4e input argument, indien-niet-gevonden, is optioneel. Hier vul je simpelweg in wat je als output wilt als de X.ZOEKEN-functie niets vindt.

=X.ZOEKEN( F5; $B$3:$B$9; $D$3:$D$9; "data?")// cel G5, output: data?

In dit voorbeeld wordt er niets gevonden voor afdeling C. In dat geval willen we de waarde data? zien.

Optioneel input argument 5: overeenkomstmodus

Het 5e optionele input argument, overeenkomstmodus, werkt vergelijkbaar als benaderen voor VERT.ZOEKEN.

=X.ZOEKEN( C1; $C$7:$C$10; $D$7:$D$10; ; 0; )// cel D1, output: #N/A
=X.ZOEKEN( C2; $C$7:$C$10; $D$7:$D$10; ; -1; )// cel D2, output: Silver
=X.ZOEKEN( C3; $C$7:$C$10; $D$7:$D$10; ; 1; )// cel D3, output: Gold

Er zijn 4 mogelijkheden voor overeenkomstmodus:

• Waarde 0 (of als leeg): zoek exact, geen benaderen
• Waarde -1: als exacte match bestaat neem die, anders de waarde die kleiner is. Dit is vergelijkbaar met benaderen door VERT.ZOEKEN.
• Waarde 1: als exacte match bestaat neem die, anders de waarde die groter is.
• Waarde 2: een "wildcard" match. Hiermee kun je bijvoorbeeld een gedeeltelijke match doen. Bijvoorbeeld "*A*" voor eerste argument zoekwaarde in combinatie met 2 voor overeenkomstmodus heeft een match voor alle cellen die een A (of a) bevatten.

Vergelijk X.ZOEKEN met VERT.ZOEKEN:

// Exact zoeken:
=X.ZOEKEN( C1; $C$7:$C$10; $D$7:$D$10; ; 0; )
=VERT.ZOEKEN( C1; $C$7:$D$10; 2; ONWAAR)
// Benaderen ( exact of lager):
=X.ZOEKEN( C2; $C$7:$C$10; $D$7:$D$10; ; -1; )
=VERT.ZOEKEN( C2; $C$7:$D$10; 2; WAAR)

Let erop dat bij VERT.ZOEKEN we ook kolom D in het tweede input argument moeten hebben staan. We definiëren in het derde input argument dat we de tweede kolom willen hebben als output.

Als je niets definieert voor overeenkomstmodus, dan gaat X.ZOEKEN ervan uit dat je exact wilt zoeken. Dit is anders dan bij VERT.ZOEKEN, dat er dan vanuit gaat dat je wilt benaderen als je niets voor benaderen definieert.

Optioneel input argument 6: zoekmodus

Het laatste optionele argument is zoekmodus:

• Waarde 1 (of als leeg): zoek "gewoon" van boven naar beneden
• Waarde -1: zoek van beneden naar boven. Vaak handig als je af en toe data toevoegt en de laatste data wilt hebben
• Waarde -2 en 2: gebruik je alleen in hele specifieke situaties, alleen als je grote hoeveelheden gesorteerde data hebt

=X.ZOEKEN( F3; $B$3:$B$9; $D$3:$D$9; ; ; 1)// cel G3, output: 12
=X.ZOEKEN( F4; $B$3:$B$9; $D$3:$D$9; ; ; -1)// cel G4, output: 8

Meerdere resultaten weergeven, of matrix retourneren

X.ZOEKEN kan ook een matrix als output geven, in plaats van een enkele cel. Dit als alternatief voor meerdere keren dezelfde functie kopiëren.

=X.ZOEKEN( F3; B3:B9; C3:D9; "data?"; 0; -1)// cel G3, output: Apr

De functie vult in dit voorbeeld 2 cellen; de cel waar de functie in staat en de cel rechts ervan. Dit omdat we in het derde input argument, matrix-retourneren, een input hebben gegeven die 2 kolommen breed is.

Let erop dat de cellen waar de X.ZOEKEN-functie zijn data kwijt wil, ook leeg zijn. Anders krijg je een #OVERLOPEN!-foutmelding. De functie overschrijft dus geen cellenwaar al data in stond.

Tweedimensionaal zoeken met X.ZOEKEN

Als je een tweedimensionale tabel hebt, en op beide assen flexibel wilt kunnen zoeken, dan kan dat ook met X.ZOEKEN. Je doet dit door twee keer X.ZOEKEN in elkaar te nesten:

=X.ZOEKEN( D2; D5:G5; X.ZOEKEN( C2; C6:C8; D6:G8))// cel E2, output: 10

De binnenste X.ZOEKEN-functie zoekt naar de afdeling en geeft als output de hele rij van die afdeling.

De buitenste X.ZOEKEN zoekt vervolgens in die rij de juiste maand.

=X.ZOEKEN( D2; D5:G5; X.ZOEKEN( C2; C6:C8; D6:G8))
=X.ZOEKEN( D2; D5:G5; X.ZOEKEN( "B"; C6:C8; D6:G8))
=X.ZOEKEN( D2; D5:G5; D7:G7)// Rij 7 uit binnenste X.ZOEKEN
=X.ZOEKEN( "Mrt"; D5:G5; D7:G7)// Geeft 10

X.ZOEKEN met meerdere criteria

Er zijn situaties waarin we meerdere criteria willen checken, bijvoorbeeld een match in de ene kolom, en een match in een andere kolom.

Bij VERT.ZOEKEN hadden we hiervoor een hulpkolom nodig, voor X.ZOEKEN niet:

=X.ZOEKEN( 1; ( C7:C10=D2)*( D7:D10=D3); E7:E10)// cel D4, output: 17

In eerste instantie ziet deze formule er wat intimiderend uit.

Deze formule werkt stap voor stap als volgt:

// 1. Start: zoek waarde 1 in berekening uit tweede input, geef bijbehorende waarde uit E7:E10
=X.ZOEKEN( 1; ( C7:C10=D2)*( D7:D10=D3); E7:E10)// 1.
// 2. Start berekening tussen haakjes van tweede input
=X.ZOEKEN( 1; ( C7:C10="Dennis")*( D7:D10="HR"); E7:E10)
// 3. Evalueer alles tussen haakjes naar array. Als bv. waarde in kolom C gelijk is aan "Dennis" dan 1, anders 0
=X.ZOEKEN( 1; ( {1; 0; 1; 0})*( {0; 0; 1; 1}); E7:E10)
// 4. Vermenigvuldig arrays met elkaar. Alleen 1 als beide arrays 1 zijn, anders 0. Hierin zoeken we waarde 1 ( eerste input argument)
=X.ZOEKEN( 1; {0; 0; 1; 0}; E7:E10)
// 5. Alleen "1" voor 3e cel van E7:E10, oftwel E9
="17"// waarde van cel E9

Nog een groot voordeel is dat we ingewikkeldere logica zouden kunnen toepassen. Denk aan "Als kolom A groter dan 3, kolom B gelijk aan HR, kolom C..."