We zijn bijna aan het einde van de 18 blogs die ik jullie deze maand heb voorgesteld.
En natuurlijk willen we in stijl eindigen. Morgen wat vuurwerk en vandaag een mooie lichtshow. J
Verticaal zoeken of de alom tegenwoordige Vlookup. Wie gebruikt hem niet? Enkele maanden geleden was ik bij een klant die in Excel een fiche bijhield van al zijn producten. Een gigantisch bestand met een massa aan worksheets. Elke sheet was een one-pager met productspecificaties en een bijhorende foto. De vraag werd gesteld om hier een Access database van te maken aangezien het bestand te groot werd.
Ik heb de klant laten zien hoe je met de formules Index en Match (de 2 formules die samen het snellere broertje van Vlookup vormen) naar een foto kan zoeken. Even later was de basis gelegd voor een Excelbestand met nog maar 1 productfiche. Daarnaast werd een blad opgemaakt met een producttabel waarbij alle producteigenschappen in kolommen werden gestockeerd. Een laatste blad bevatte alle foto’s van de producten. Dus, 3 sheets in totaal om eender welke productfiche op te halen.
In de blog van vandaag zal ik jullie laten zien hoe je zo een index-match combinatie dient op te zetten. Een aantal technieken die we gebruiken heb je vorige week reeds geleerd in de blogs over namen toekennen en dropdowns maken. Als je goed voorbereid wil zijn, kan je deze blogs best even opnieuw raadplegen.
There we go! (O ja, misschien nog wat fotootjes verzamelen voor we starten)
Bereid een blad voor dat er ongeveer als volgt uitziet.
In cel B2 zetten we de naam van het logo dat we zoeken. In cel B4 zetten we 1 van de logo’s (het maakt niet uit welk). Let wel op dat deze cel groot genoeg is voor elk logo.
Maak nu ergens een verticale lijst aan met de namen van de logo’s. Ernaast plaats je de logo’s zelf (zorg dat ze netjes in de cel passen). Meestal zet ikzelf dit lijstje op een apart blad.
De tekst naast de foto’s dient exact overeen te komen met de namen die je straks gaat intypen in B2.
Geef via ‘Formulas – Define name’ aan elke cel (E8, E9, E10) de naam die met het logo overeenkomt.
Opgelet, geef de cel een naam en niet de foto. E8 heeft als naam “Xylos”, E9 heeft als naam “Inia” en E10 heeft als naam “Neo”.
Zowel de inhoud van cel ‘B2’, de tekst in kolom ‘D’ en de naam van cel achter het logo hebben een identieke schrijfwijze.
Zo, dat volstaat als voorbereiding.
Voor we verder gaan, wil ik heel even een korte uitleg geven over de functie ‘Vlookup’ en daarbij het verschil en de gelijkenis weergeven met de formules ‘Index’ en ‘Match’.
Om te beginnen, geef je via Vlookup aan dat er verticaal naar een waarde moet gezocht worden en dat de oplossing horizontaal in een bepaalde kolom terug te vinden is. 2 bewegingen in 1 matrix dus. In mijn Excel opleidingen laat ik vaak zien dat je dit ook kan met Index & Match. In de meeste gevallen is dit zelfs een snellere oplossing.
Wat is dan de functie van Index en Match? Met index geef je aan dat je uit een reeks van waarden de x-ste waarde wil opvragen. Met de Match functie vraag je om in een lijst van waarden te zoeken naar de positie van de waarde die je meegeeft. We gaan de Match functie gebruiken om te zoeken in kolom E naar de waarde van cel B2, bv de waarde van “Neo” (zoals in bovenstaand figuur). De oplossing zal in dit geval 3 zijn.
Dit getal (3) gebruiken we binnen de Index functie om het derde element van de reeks aan te duiden. De Match functie speelt dus de rol van één van de argumenten van de Index functie. Deze zal in kolom E gebruikt worden en het getal 3 zal de 3de cel uit de groep ophalen. Het resultaat hiervan is de tekst “Neo”.
In onderstaand voorbeeld geeft match in F2 als oplossing ‘2’ (Zie cel G2). Via de Index-formule in F3 geven we aan dat we uit de lijst ‘C5:C7’ het 2de element willen (de functies zijn een beetje omgekeerd aan elkaar dus). Het resultaat hiervan is de tekst “Inia”.
Kan je voor de logo’s dan niet een gewone Vlookup gebruiken? Nee, toch niet. Kijk even naar de volgende 2 screenshots. Index kan naast een waarde ook refereren naar een gebied, terwijl Vlookup enkel een waarde kan teruggeven.
Voor de oplossing van deze blog dienen we de index formule op te slaan in een naam. De formule toepassen in Excel zal enkel de waarde van de cel ophalen, maar niet de referentie bevatten.
Kies ‘Define Name’ in de formulebalk en geef volgende gegevens in.
Deze functie dien je als volgt te lezen:
In de Match functie is het eerste argument de cel B2 (de cel met de tekst van het logo dat je zoekt) en het tweede argument is de lijst in kolom ‘D’ waarin je dezelfde waarde wil terugvinden.
De oplossing van de Match functie is dus een nummer of m.a.w. de positie.
In de Index functie geven we als eerste argument het gebied aan waar de foto’s staan, namelijk de cellen in kolom E. De Match functie van daarnet geeft dus aan op welke positie je waarde staat (of de referentie naar die cel).
Door dit alles in een naam te plaatsen, kan je later via die naam een referentie bekomen naar de oplossing.
Klik nu op de foto die in cel B4 staat. Ga naar de formulebalk en typ de formule “=MijnLogo”.
Verander de waarde in B2 en merk op dat het logo wijzigt.
Tip: Lees in deze context zeker eens de blog http://blog.xylos.com/2014/02/powerpivot-het-einde-van-de-vlookup/, van mijn collega Johan Vermeire.
Morgen: Dag 18: Paswoorden zelf verwijderen (mag dat wel ?).
