Verticaal zoeken Index en Vergelijken

Excel | Verticaal zoeken Index en Vergelijken

In artikel ga ik er vanuit dat je weet hoe Verticaal Zoeken (“vLookup”) werkt. Als je je hier eerst nog wilt verdiepen in Verticaal zoeken dan kan je hier nog even kijken.


Verticaal zoeken (“vLookup”) het alternatief

In dit artikel wordt het tijd om Verticaal zoeken (“vLookup”) te verlaten en het alternatief voor Verticaal zoeken (“vLookup”) te gebruiken. Wat is het voordeel van dit alternatief vraag je je misschien af? Dat lees je hieronder.

Wij gebruiken onderstaand overzicht als voorbeeld voor dit artikel.

Excel 2007 : vLookup | Verticaal zoeken


Met verticaal zoeken deden we het zo

Laten we zeggen dat je net als in het voorbeeld in kolom A klantnummers hebt staan, alle data die na kolom A komt maakt verder niet zoveel uit. Want zodra je een klantnummer hebt kan je alles uit dit voorbeeld met Verticaal Zoeken (“vLookup”) lokaliseren. De formule van Verticaal Zoeken (“vLookup”):

Zoals je in dit (zie link) artikel hebt gelezen zegt deze formule eigenlijk: “Zoek binnen het gedefinieerde zoekgebied naar een regel met waarde X in de eerste kolom van het zoekgebied.”.

Kan dit ook met het alternatief?

 


Waarom zou ik het alternatief gebruiken?

Op een dag kom je in de situatie dat je een email adres hebt en je daarbij graag het klantnummer wilt zoeken (Knippen @ degoudenschaar.nl).

Kijk nog even naar het voorbeeld hierboven en je ziet het e-mailadres in kolom E en het klantnummer in kolom A.

Wanneer je hier Verticaal Zoeken (“vLookup”) toepast zit je waarschijnlijk opgescheept met een foutmelding. Excel komt er met Verticaal zoeken op deze manier niet uit. Vanwege de simpele reden: “Verticaal zoeken” werkt alleen naar rechts en niet naar links.

 

Workaround

Overigens lossen de meeste mensen dit probleem op door even kolom E te kopiëren en voor kolom A te plaatsen en dan alsnog Verticaal Zoeken (“vLookup”) uit te voeren. De echte groot-gebruikers zullen deze handeling wel kunnen dromen denk ik. Je vraagt je dan ook meteen af, “Is hier geen andere oplossing voor?”. Ja die is er zeker maar niet met Verticaal Zoeken. (“vLookup”). Vandaar het alternatief.

 


Index (“Index”) en Vergelijken (“Match”)

Nu je weet dat verticaal zoeken zo zijn beperkingen heeft is het een goed moment om te starten met het alternatief. Het zogenoemde alternatief bestaat uit 2 functies, namelijk: Index en Vergelijken (“Match”).

De meesten mensen gebruiken deze 2 functies niet elke dag dus daarom leg ik nog even in normaal Nederlands uit wat deze 2 functies los van elkaar doen.

 

Wat doet de index (“Index”) functie?

De index functie zoekt net als Verticaal zoeken een opgegeven waarde maar werkt zowel naar links als rechts en over meerdere cellen en/of kolommen. Het voordeel van de index functie is dat de regel en het kolom nummer variabel kunnen zijn. Hierover later meer.

De Index functie gebruikt 3 parameters.
1) Zoekgebied
2) Regelnummer
3) Kolomnummer

Excel 2007 : vLookup | Verticaal zoeken

Wat de index functie eigenlijk zegt is, ZOEK in A2:E6 op regelnummer 3 en in kolom nummer 5 en geef mij daarvan de waarde. Het lijkt enigszins al iets op Verticaal Zoeken (“vLookup”). Zoals je ziet is het eigenlijk best makkelijk als het in begrijpbare taal word uitgelegd.

Als we de functie Index toepassen op bovenstaand voorbeeld :
1) Zoekgebied: A2:E6
2) Regelnummer: 3
3) Kolom nummer: 5
Het resultaat is dan de waarde dat in cel E4 staat.

Met alleen de INDEX functie ben je er nog niet, want als we even teruggaan naar originele fictieve probleem dan zul je nog niet heel ver komen met alleen de Index functie. Je weet immers het e-mailadres maar wilt daar graag ook het klantnummer van weten. Wanneer we de INDEX functie gaan toepassen op ons probleem zou het onderstaande formule worden:

We weten niet op welke regel het e-mailadres staat dus hier staat nu een ?“. Zoals je zult begrijpen is de formule op deze manier nutteloos tenzij je zelf h hier staat nu een ?“. Zoals et regelnummer invult. Dit kan natuurlijk in dit voorbeeld maar niet als je 100.000 regels en kolommen hebt.

 

Wat doet de Vergelijken (“Match”) functie?

De vergelijken functie doet eigelijk niets anders dan het regelnummer weergeven van de gevonden waarde. Laten we die nou net missen, dus dat komt goed uit.

De Vergelijken (“Match”) functie gebruikt 3 parameters.
1) Zoekwaarde
2) Zoekgebied
3) Waar (“True”) of Onwaar (“False”)

Wat deze functie eigenlijk doet is: Zoek Waarde X in het opgegeven zoekgebied (1 kolom of 1 rij) en geef mij de lokatie, het regelnummer, van de vondst.

Excel 2007 : vLookup | Verticaal zoeken

 

Als we de functie Vergelijken (“Match”) toepassen op bovenstaand voorbeeld :
1) Zoekwaarde : “Knippen @ degoudenschaar.nl”
2) Zoekgebied : E2:E6
3) Onwaar (“False”)
Het resultaat is dan 6.

Je moet hier opletten dat je 1 kolom of 1 rij selecteert, meerdere rijen of kolommen werken hier niet. Natuurlijk kan je de zoekwaarde ook via een cel verwijzing ophalen. Dan kan de zoekwaarde ook A10 worden.

De formule toegepast op ons voorbeeld is dan


Index en vergelijken gecombineerd

Als we de Index en de Vergelijken (“Match”) functie combineren hebben we een oplossing en daarmee een alternatief voor Verticaal zoeken (“vLookup”).

Even terug naar de INDEX functie, wij hadden deze formule gemaakt : =INDEX(A2:E6;?;1) Maar in deze formule stond een “?“. Het vraagteken stond voor de onbekende regel. Met de Vergelijken (“Match”) functie vinden wij die onbekende regel. Vervang nu het vraagteken met de Vergelijken (“Match”) functie zoals we de net hebben gemaakt : =MATCH(A10;E2:E6;FALSE)

De combinatie van die 2 maakt dan deze formule:


Het resultaat op ons probleem is 1008.


2 Reacties

  1. Erik
    | Beantwoorden

    Ik snap er niks van.

  2. Linda
    | Beantwoorden

    Super handig uitgelegd! Probleem opgelost!

Heb je nog opmerkingen of vragen? Je kunt ze in de box hieronder of op het forum kwijt.