In een eerdere blog heb ik uitgelegd hoe je verticaal kunt zoeken in Excel. Hiermee kun je een waarde opzoeken in een tabel. Als je deze functie veel gebruikt, zal je inmiddels gemerkt hebben dat dit behoorlijk traag kan zijn als je bron waarin je zoekt heel groot is. Er is een manier om je zoekopdrachten aanzienlijk te versnellen: efficiënter zoeken in Excel met INDEX MATCH.
Zoals ik al zei: zoeken in Excel met verticaal zoeken (VERT.ZOEKEN / VLOOKUP) kan behoorlijk langzaam zijn. Ik heb zelf ervaring met brongegevens die tienduizenden rijen lang zijn, en -tig kolommen tellen, en je wordt niet altijd blij van de duur van de zoekopdracht. Zeker niet als je veel van dat soort zoekopdrachten doet en je waardes moet verversen. De reden hiervan is dat een verticaal (of horizontaal) zoeken opdracht steeds weer je hele kolom of rij door moet. Hoe handig zou het zijn als je ineens precies naar de juiste cel kon wijzen? En dat kan!
Vereisten
Er is één maar aan de oplossing met INDEX MATCH: je brongegevens moeten voorzien zijn van identificeerbare koppen waar je in kunt zoeken, evenals een kolom gevuld met waardes waar je op kunt zoeken. Mis je kolomhoofden dan is verticaal zoeken toch je alternatief, en mis je een kolom met waardes waar je in kunt zoeken, dan is horizontaal zoeken je alternatief. Maar ja, die zijn dus langzamer.
Uitgangspunt
Ik hanteer voor het onderstaande voorbeeld deze tabel van fictieve personeelsgegevens. We gaan in deze tabel zoeken naar het salaris in 2014 van A. de Groot, personeelsnummer 10456:
Doel
Om het gewenste salaris te vinden, gaan we de INDEX functie gebruiken. Deze functie kent de volgende syntax:
=INDEX(tabel,rijnummer,kolomnummer)
We zullen dus eerst de juiste rij en kolomnummers moeten vinden, willen we de INDEX functie kunnen uitvoeren. Dat doen we met behulp van de MATCH functie – ofwel de VERGELIJKEN functie in de Nederlandse versie (ik gebruik voor het gemak even MATCH in de rest van dit artikel). De match functie kent de volgende syntax:
=MATCH(zoekwaarde,bereik,matchtype)
We gaan op zoek naar Salaris2014 van medewerker 10456. Ik heb het rijnummer en kolomnummer nodig. Ik maak voor de duidelijkheid even een zoekgedeelte in de kolommen H tot en met K. Ik vul met de hand het personeelsnummer en Salaris2014 in als zoekgegeven:
>> Lees verder: STAP 1
Geweldige uitleg Michel, bedankt!
Graag gedaan 🙂
Het is mij echter onduidelijk hoe die formule er nu uit komt te zien….
Hi!
Op de 3e pagina vind je deze uiteindelijke functie:
=INDEX( A1:F8; MATCH(I2;A1:A8;0); MATCH(I3;A1:F1;0) )
Oftewel:
– binnen de matrix A1:F8,
– zoek in de kolom A1:A8 naar rijnummer met het personeelsnummer I2 (1e MATCH),
– en het kolomnummer waarin gegeven I3 staat (2e MATCH),
– en geef die waarde weer (resultaat van de INDEX).
Hoop dat dit helpt!
Het is mij echter onduidelijk hoe deze formule nu er uit komt te zien.
Hallo,
altijd erg interessant om deze site te bezoeken.
ik heb ook een zoek-vraag.
ik heb een tabel van 10 bij 10.
in kolom a staan een aantal verkopers (in de velden a2 tm a10)
op rij 1 staat een aantal producten (in de velden b1 tm j1)
in de tabel wordt bij elke verkoper het aantal verkochte producten gemeld.
iedere verkoper verkoopt slechts een product. dus op een rij staat maar 1 getal.
ik wil kolom k, dus rechts van de tabel, de naam van het product zien (die naam staat dus op de 1e rij) dat deze verkoper heeft verkocht.
hoe krijg ik dat voor elkaar?
b tm j een product.
in die tabel is per rij slechts een kolom gevuld.
ik ben op zoek naar een formule zodat ik in kolom k (rechts van de tabel) op elke rij het product komt te staan waar de ot te staan welk product de net naast de
Hi Ardie,
Sorry dat je zo lang moest wachten op antwoord.
Aangezien je zegt dat elke verkoper in kolom A maar 1 product verkoopt, zou ik een trucje gebruiken. Eerst bepalen we met de MATCH functie welke kolom een waarde heeft, gebruik makende van de MAX functie. Vervolgens halen met INDEX de productnaam op. Dus:
MATCH vraagt om 3 velden:
1. Waar zoek je naar: in dit geval zou ik dus MAX gebruiken, omdat maar 1 van de kolommen een waarde heeft, dus MAX(B2:J2)
2. De lookup-tabel: deze geldt per rij, voor de eerste verkoper dan ook B2:J2
3. Het zoektype: in dit geval 0 (exacte match)
INDEX vraagt om 3 velden:
1. De zoekmatrix ($B$1:$J$10)
2. Het rijnummer van de op te zoeken waarde: altijd rij 1 in dit geval
3. Het kolomnummer van de op te zoeken waarde: in dit geval de MATCH functie en het resultaat.
In K2 ziet de formule er dan als volgt uit:
=INDEX($B$1:$J$10;1;MATCH(MAX(B2:J2);B2:J2;0))
Als je deze copy-paste naar de rijen eronder, ziet de formule in K3 er bijvoorbeeld als volgt uit:
=INDEX($B$1:$J$10;1;MATCH(MAX(B3:J3);B3:J3;0))
Ik hoop dat het hiermee lukt!
Groet,
Michel Boks
Hallo,
Maar wat nu als alle rijen wel met waarden gevuld zijn. Ik heb een tabel van 9 bij 13 waarbij rij 1 en kolom 1 namen hebben. de rest van de tabel (8 x 12) zijn gevuld met getallen. Ik wil nu graag de namen van de eerste rij en kolom van een bepaald getal uit de tabel.
Mvg