Hoe te zoeken in een matrix in Excel?

Excel kent veel mogelijkheden om te zoeken in tabellen, echter het zoeken in een matrix is (voor zover ik kan nagaan) niet direkt middels een aparte functie te doen. Ik heb bij diverse mensen hier naar gevraagd (ook tijdens cursussen) maar niemand heeft mij de oplossing kunnen geven. Dus zelf maar aan het puzzelen gegaan met dit als resultaat.

De zoekfuncties in Excel

Excel kent twee formules waarmee in tabellen kan worden gezocht. Een functie voor horizontaal zoeken (=horiz.zoeken) en een functie voor vertikaal zoeken (=vert.zoeken). De eerste zoekt in een rij met gegevens naar een opgegeven waarde de tweede zoekt in een kolom met gegevens naar een opgegeven waarde.

De verticale zoekfunctie

Zoals gezegd zoekt de verticale zoekfunctie in een kolom naar de celinhoud achter een ingegeven waarde.


Dus als wordt gezocht op artikelnummer 101.03 dan vindt de functie een van de daarachterliggende aantallen. Het aantal (10) in maat 48 wordt gevonden indien als zoekwaarde 2 wordt ingevoerd, het aantal (20) in maat 50 indien als zoekwaarde 3 wordt ingevuld.

De horizontale zoekfunctie

Deze functie zoekt dus in een rij naar de opgegeven zoekwaarde. Wordt in bovenstaande tabel op maat 48 gezocht dan wordt afhankelijk van de zoekwaarde een van de daaronder gelegen getallen gevonden (overigens geeft zoekwaarde 1 in dit geval 48 als uitkomst).

Zoeken in een matrix

Het zou toch makkelijk zijn indien in een keer zowel op maat als op artikelnummer kan worden gezocht. Dat kan door beide functies met elkaar te combineren. Er moet dan echter wel een extra kolom (of rij) worden ingevoegd waarin een indexgetal wordt opgenomen.

De tabel komt er dan als volgt uit te zien:

U ziet dat achter het artikelnummer een extra kolom is ingevoegd. Door de waarde uit deze kolom bij een artikelnummer op te zoeken weet ik op welke rij de aantallen per maat bij dit artikel worden vermeld.

Dus als ik wil weten wat de voorraad is van artikel 101.03 in maat 52 dan weet ik dat ik op de derde regel moet zoeken. Omdat de regel met de maataanduidingen ook meetelt moet ik dus als zoekwaarde 4 (3+1) nemen.

De formule ziet er dan als volgt uit: =HORIZ.ZOEKEN("52";"bereik matrix";VERT.ZOEKEN("101.03";"bereik matrix";2;0)+1;0).

Let op: de matrix waarin wordt gezocht is niet in beide gevallen dezelfde. Bij het zoeken naar een artikelnummer kan de grootte van de matrix worden beperkt tot de eerste twee kolommen; bij het zoeken naar de hoeveelheid per maat is het zoekbereik beperkt tot de kolommen waar de maten in staan.

Maak in uw sheet een cel waarin het artikelnummer kan worden ingevoerd en een cel waarin de maat kan worden opgegeven en plaats in een derde cel de hierboven weergegeven formule. Blokkeer alle andere velden behalve die waar het artikelnummer en de maat ingevoerd moeten worden en u heeft een simpele manier om in een matrix te zoeken.