Het is gemakkelijk om waarde op te zoeken met één unieke sleutel in een tabel. We kunnen gewoon de VERT.ZOEKEN-functie gebruiken. Maar als u die unieke kolom niet in uw gegevens heeft en in meerdere kolommen moet zoeken om een waarde te matchen, helpt VERT.ZOEKEN niet.
Dus om een waarde op te zoeken in een tabel met meerdere criteria, gebruiken we de formule INDEX-MATCH-INDEX.
Algemene formule voor het opzoeken van meerdere criteria
= INDEX(lookup_range,MATCH(1, INDEX((criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0,1),0))
lookup_range: Dit is het bereik waaruit u waarde wilt ophalen.
Criteria1, Criteria2, Criteria N: Dit zijn de criteria die u wilt matchen in bereik1, bereik2 en bereik N. U kunt maximaal 270 criteria hebben - bereikparen.
Bereik1, bereik2, bereikN : Dit zijn de bereiken waarin u aan uw respectieve criteria zult voldoen.
Hoe het zal werken? Laten we zien…
INDEX en MATCH met meerdere criteria Voorbeeld
Hier heb ik een gegevenstabel. Ik wil de naam van de klant ophalen met behulp van Boekingsdatum, Bouwer en Gebied. Dus hier heb ik drie criteria en één opzoekbereik.
Schrijf deze formule in cel I4 en druk op enter.
=INDEX(E2:E16,MATCH(1, INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0))
Hoe het werkt:
We weten al hoe de INDEX- en MATCH-functie werken in EXCEL, dus dat ga ik hier niet uitleggen. We zullen het hebben over de truc die we hier hebben gebruikt.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Het belangrijkste is dit. Elk deel van deze instructie retourneert een array van true false.
Wanneer booleaanse waarden worden vermenigvuldigd, geven ze een array van 0 en 1 terug. Vermenigvuldiging werkt als AND-operator. Hense wanneer alle waarden alleen waar zijn, retourneert het 1 else 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Dit komt helemaal terug
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}* {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}* {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Wat zich vertaalt in
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INHOUDSOPGAVE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX-functie retourneert dezelfde array ({0;0;0;0;0;0 ;0;1;0;0;0;0;0;0;0}) naar MATCH-functie als opzoekarray.
BIJ ELKAAR PASSEN(1,INHOUDSOPGAVE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): MATCH-functie zoekt naar 1 in array {0;0;0;0;0;0 ;0;1;0;0;0;0;0;0;0}. En retourneert het indexnummer van de eerste 1 gevonden in de array. Dat is hier 8.
INHOUDSOPGAVE(E2:E16,BIJ ELKAAR PASSEN(1,INHOUDSOPGAVE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Ten slotte zal INDEX de waarde retourneren uit het opgegeven bereik (E2:E16) bij gevonden index (8).
Eenvoudig????. Sorry, kon het niet eenvoudiger maken.
Matrix-oplossing
Als u consequent op CTRL + SHIFT + ENTER kunt drukken, kunt u de innerlijke INDEX-functie elimineren. Schrijf gewoon deze formule en druk op CTRL + SHIFT + ENTER.
=INDEX(E2:E16,MATCH(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0))
Generieke matrixformule voor het opzoeken van meerdere criteria
=INDEX(lookup_range,MATCH(1,(criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0))
Formule werkt hetzelfde als bovenstaande uitleg.
Ik heb mijn best gedaan om het zo eenvoudig mogelijk uit te leggen. Maar als ik niet duidelijk genoeg was, laat het me dan weten in het commentaargedeelte hieronder. Je hoeft trouwens niet te weten hoe de motor werkt om een auto te besturen. Je moet alleen weten hoe je ermee moet rijden. En dat weet je heel goed.
Top 5-waarden opzoeken met dubbele waarden met INDEX-MATCH in Excel
Meerdere waarden VERT.ZOEKEN in Excel
Hoe te VERT.ZOEKEN met Dynamic Col Index in Excel
VERT.ZOEKEN gebruiken vanuit twee of meer opzoektabellen in Excel
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken?
Hoe de AANTAL.ALS-functie in Excel te gebruiken?
Hoe de SUMIF-functie in Excel te gebruiken?