vlookup pro: hoe 2 tabellen errorproof linken in excel met vlookup en match functie

Posted on september 24, 2008 door

3


Het moet zeker al gebeurd zijn: je hebt je tabellen gelinkt met de vlookup functie zoals we in één van onze vorige blogs (zie Hoe 2 tabellen linken met Excel) hebben uitgelegd. En hoera hoera, het werkt! Na een tijdje merk je dat plots het resultaat is veranderd en de vlookup functie naar de verkeerde kolom verwijst. Hoe is dit in godsnaam mogelijk?

Een typische oorzaak van dit probleem is het invoegen kolommen, zodat de verwijzing in de vlookup niet meer klopt (typisch door de nietsvermoedende collega die daardoor alles in de war stuurt, grrr…).

Er is relatief eenvoudige oplossing die dit probleem voor eens en voor altijd uit de wereld helpt: combineer de ‘vlookup’ functie met een ‘match’ functie.

Laten we teruggaan naar het voorbeeld van de vorige blog: hierin koppelen we de tabel klanten met de tabel producten (en meer bepaald de prijs):

image

Een analyse van de formule in verschillende stappen:

1. We zoeken $B4 ofwel ‘product code’ 12345-26

2. De tabel waarin we zoeken is A1 tot D13 in de prijslijst sheet:

image

3. We wensen de prijs terug te vinden, dus kiezen we voor kolom D en dit is de 4de kolom

Het probleem is dat als we in de tabel ‘prijslijst en kolom zouden toevoegen, dan zal je zien dat het resultaat niet meer klopt:

image

image

Kolom 4 uit de vlookup functie verwijst nu nog steeds naar kolom D, nu echter de voorraad in plaats van de prijs.

In de plaats van de referentie van de kolom hardcoded in te geven, kunnen we ze ook eenvoudig laten berekenen door volgende functie:

match(“prijs (EUR)”;prijslijst!$A$1:$E$1;0)

Opgelet: de (unieke) naam van de header rij wordt hierdoor wel zeer belangrijk

Als we vervolgens deze functie in de vlookup innesten, zijn al onze problemen opgelost.

image

Advertenties