Deze voorbeelden leren u hoe u meerdere criteria kunt opzoeken, een specifieke instantie of alle overeenkomsten kunt retourneren, dynamische Vlookup kunt uitvoeren in meerdere bladen en meer.
Het is het tweede deel van de serie dat u zal helpen de kracht van Excel VERT.ZOEKEN te benutten. De voorbeelden impliceren dat u weet hoe deze functie werkt. Als dit niet het geval is, is het logisch om te beginnen met het basisgebruik van VERT.ZOEKEN in Excel.
Voordat ik verder ga, wil ik u kort de syntaxis herinneren:
Nu iedereen op dezelfde pagina zit, laten we de geavanceerde VLOOKUP-formule voorbeelden eens nader bekijken:
- Hoe u meerdere criteria in Excel kunt bekijken
- Vlookup en return nde match
- Vlookup meerdere waarden
- Vlookup gebaseerd op rij- en kolomwaarden
- Geneste Vlookup-formule
- VERT.ZOEKEN en INDIRECT om gegevens uit meerdere bladen op te halen
Meerdere criteria opzoeken
De Excel VERT.ZOEKEN-functie is erg handig als het gaat om het zoeken in een database voor een bepaalde waarde. Het mist echter een belangrijke functie: de syntaxis maakt slechts één opzoekwaarde mogelijk. Maar wat als u wilt opzoeken met verschillende voorwaarden? Er zijn een paar verschillende oplossingen waaruit u kunt kiezen.
Formule 1. VERT.ZOEKEN gebaseerd op tw o waarden
Stel dat u een lijst met bestellingen heeft en de hoeveelheid wilt vinden op basis van 2 criteria: klantnaam en product. Een complicerende factor is dat elke klant meerdere producten bestelde, zoals weergegeven in de onderstaande tabel:
Een gebruikelijke VERT.ZOEKEN-formule werkt hierin niet situatie omdat het de eerste gevonden overeenkomst retourneert op basis van een enkele opzoekwaarde die u opgeeft.
Om dit te verhelpen, kunt u een hulpkolom toevoegen en de waarden van twee opzoekkolommen (Klant en Product) daar samenvoegen. Het is belangrijk dat de hulpkolom de meest linkse kolom in de tabelmatrix is, omdat Excel daar altijd naar de opzoekwaarde zoekt.
Voeg dus een kolom toe aan de linkerkant van uw tabel en kopieer de onderstaande formule in die kolom. Dit zal de hulpkolom vullen met de waarden van de kolommen B en C (de spatie is daartussen samengevoegd voor betere leesbaarheid):
=B2&" "&C2
Gebruik vervolgens een standaard VERT.ZOEKEN-formule en plaats beide criteria in het opzoekwaarde-argument, gescheiden door een spatie:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Of voer de criteria in afzonderlijke cellen in (in ons geval G1 en G2) en voeg die cellen samen:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Zoals we een waarde uit kolom D willen retourneren, die de vierde is in de tabelmatrix, gebruiken we 4 voor col_index_num. Het argument range_lookup is ingesteld op FALSE om Vlookup een exacte match te geven. De onderstaande schermafbeelding toont het resultaat:
Als uw opzoektabel zich in een ander blad bevindt, neem dan de naam van het blad op in uw VERT.ZOEKEN-formule. Bijvoorbeeld:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
U kunt ook een benoemd bereik maken voor de opzoektabel (bijvoorbeeld Orders) om de formule gemakkelijker te maken- te lezen:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Voor meer informatie, zie Hoe u vanuit een ander blad in Excel kunt bekijken.
Formule 2. Excel VERT.ZOEKEN met meerdere voorwaarden
In theorie kunt u de bovenstaande benadering voor Vlookup op meer dan twee criteria gebruiken. H er zijn echter een paar kanttekeningen. Ten eerste is een opzoekwaarde beperkt tot 255 tekens, en ten tweede staat het ontwerp van het werkblad het toevoegen van een hulpkolom mogelijk niet toe.
Gelukkig biedt Microsoft Excel vaak meer dan één manier om hetzelfde te doen. Om meerdere criteria op te zoeken, kunt u een INDEX MATCH-combinatie of de XLOOKUP-functie gebruiken die onlangs is geïntroduceerd in Office 365.
Om bijvoorbeeld op te zoeken op basis van 3 verschillende waarden (datum, klantnaam en product), gebruik een van de volgende formules:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Waar:
Voor de gedetailleerde uitleg van de formules, zie:
- XLOOKUP met meerdere criteria
- INDEX MATCH-formule met meerdere criteria
Hoe u VERT.ZOEKEN gebruikt om een 2e, 3e of n-de overeenkomst te krijgen
Zoals u al weet, kan Excel VERT.ZOEKEN slechts één overeenkomende waarde ophalen, meer precies, het retourneert de eerst gevonden overeenkomst. Maar wat als er meerdere overeenkomsten in uw lookup-array zijn en u de 2e of 3e instantie wilt krijgen? De taak klinkt nogal ingewikkeld, maar de oplossing bestaat!
Formule 1. Vlookup Nde instantie
Stel dat u klantnamen in de ene kolom heeft, de producten die ze hebben gekocht in een andere, en u zijn op zoek naar het 2e of 3e product dat door een bepaalde klant is gekocht.
De eenvoudigste manier is om een hulpkolom aan de linkerkant van de tabel toe te voegen, zoals we in het eerste voorbeeld deden. Maar deze keer vullen we het met klantnamen en voorvalnummers zoals “John Doe1”, “John Doe2”, enz.
Gebruik de AANTAL.ALS-functie met een gemengde bereikverwijzing (de eerste referentie is absoluut en de tweede is relatief zoals $ B $ 2: B2). Aangezien de relatieve verwijzing verandert op basis van een positie van de cel waarnaar de formule wordt gekopieerd, wordt deze in rij 3 $ B $ 2: B3, in rij 4 – $ B $ 2: B4, enzovoort.
Samengevoegd met de klantnaam (B2), heeft de formule deze vorm:
=B2&COUNTIF($B$2:B2, B2)
De bovenstaande formule gaat naar A2, en vervolgens kopieer je het naar zoveel cellen als nodig is.
Voer daarna de doelnaam en het occurrencenummer in aparte cellen in (F1 en F2), en gebruik de onderstaande formule om een specifiek voorkomen op te zoeken:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formule 2. Vlookup 2e keer
Als u op zoek bent naar de 2e instantie van de opzoekwaarde, dan kunt u zonder de hulpkolom. Maak in plaats daarvan de tabelarray dynamisch door de functie INDIRECT samen met MATCH te gebruiken:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Waar:
- E1 is de opzoekwaarde
- A2: A11 is het opzoekbereik
- B11 is de laatste cel (rechtsonder) van de opzoektabel
Houd er rekening mee dat de bovenstaande formule is geschreven voor een specifiek geval waarin gegevenscellen in de opzoektabel beginnen in rij 2. Als uw tabel ergens in gebruik deze universele formule in het midden van het blad, waarbij A1 de cel linksboven is van de opzoektabel met een kolomkop:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Hoe deze formule werkt
Hier is het belangrijkste deel van de formule dat een dynamisch vlookup-bereik creëert:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
De MATCH-functie geconfigureerd voor exacte overeenkomst (0 in het laatste argument) vergelijkt de doelnaam (E1) met de lijst met namen (A2: A11) en retourneert de positie van de eerste gevonden overeenkomst, die 3 is in o uw zaak. Dit nummer zal worden gebruikt als de startrijcoördinaat voor het vlookup-bereik, dus we voegen er 2 aan toe (+1 om de eerste instantie uit te sluiten en +1 om rij 1 uit te sluiten met de kolomkoppen). Als alternatief kunt u 1 + RIJ (A1) gebruiken om de noodzakelijke aanpassing automatisch te berekenen op basis van de positie van de koprij (in ons geval A1).
Als resultaat krijgen we de volgende tekstreeks, welke INDIRECT converteert naar een bereikverwijzing:
INDIRECT("A"&5&":B11") -> A5:B11
Dit bereik gaat naar het argument table_array van VERT.ZOEKEN waardoor het gedwongen wordt om in een rij te zoeken 5, waarbij de eerste instantie van de opzoekwaarde wordt weggelaten:
VLOOKUP(E1, A5:B11, 2, FALSE)
Hoe u meerdere waarden opzoekt en retourneert in Excel
De Excel VERT.ZOEKEN-functie is ontworpen om slechts één overeenkomst te retourneren. Is er een manier om meerdere instanties te Vlookup? Ja, die is er, maar niet gemakkelijk. Dit vereist een gecombineerd gebruik van verschillende functies, zoals INDEX, SMALL en ROW is een matrixformule.
De onderstaande kan bijvoorbeeld alle exemplaren van de opzoekwaarde F2 in het opzoekbereik B2: B16 vinden en retourneren meerdere overeenkomsten uit kolom C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Er zijn 2 manieren om de formule in uw werkblad in te voeren:
- Typ de formule in de eerste cel, druk op Ctrl + Shift + Enter en sleep deze omlaag naar nog een paar cellen.
- Selecteer meerdere aangrenzende cellen in een enkele kolom (F1: F11 in de schermafbeelding hieronder), typ de formule en druk op Ctrl + Shift + Enter om deze te voltooien.
Hoe dan ook, het aantal cellen waarin u de formule invoert, moet gelijk zijn aan of groter zijn dan het maximale aantal mogelijke overeenkomsten.
Voor de gedetailleerde uitleg van de formulelogica en meer voorbeelden, zie Meerdere waarden VERT.ZOEKEN in Excel .
Vlookup in rijen en kolommen (zoeken in twee richtingen)
Opzoeken in twee richtingen (ook wel matrix-lookup of 2-dimensionale lookup genoemd) is een mooi woord om een waarde op te zoeken op het snijpunt van een bepaalde rij en kolom.Er zijn een paar verschillende manieren om tweedimensionaal op te zoeken in Excel, maar aangezien de focus van deze tutorial op de functie VERT.ZOEKEN ligt, zullen we deze natuurlijk gebruiken.
Voor dit voorbeeld nemen we de onderstaande tabel met maandelijkse verkopen en werk een VERT.ZOEKEN-formule uit om het verkoopcijfer voor een specifiek artikel in een bepaalde maand op te halen.
Met artikelnamen in A2: A9, maandnamen in B1: F1, het doelitem in I1 en de doelmaand in I2 gaat de formule als volgt:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Hoe deze formule werkt
De kern van de formule is de standaard VERT.ZOEKEN-functie die zoekt naar een exacte overeenkomst met de opzoekwaarde in I1. Maar aangezien we het niet weten in welke kolom de verkopen voor een specifieke maand precies staan, kunnen we het kolomnummer niet rechtstreeks aan het argument col_index_num leveren. Om die kolom te vinden, gebruiken we de volgende MATCH-functie:
MATCH(I2, A1:F1, 0)
In het Engels vertaald, zegt de formule: opzoeken de I2-waarde in A1: F1 en retourneert de relatieve positie in de array. Door 0 op te geven bij het derde argument, geef je MATCH de opdracht om de waarde te vinden die exact gelijk is aan de opzoekwaarde (het is alsof je FALSE gebruikt voor het argument range_lookup van VERT.ZOEKEN).
Aangezien Mar in de 4e kolom staat in de lookup-array geeft de MATCH-functie 4 terug, die rechtstreeks naar het col_index_num-argument van VERT.ZOEKEN gaat:
VLOOKUP(I1, A2:F9, 4, FALSE)
Let op dat hoewel de maandnamen beginnen in kolom B, we A1: I1 gebruiken voor de lookup-array. Dit wordt gedaan om ervoor te zorgen dat het getal dat door MATCH wordt geretourneerd overeenkomt met de positie van de kolom in table_array van VERT.ZOEKEN.
Zie INDEX MATCH MATCH en andere formules voor tweedimensionale zoekacties voor meer manieren om matrixzoekopdrachten in Excel uit te voeren.
Meerdere Vlookups uitvoeren in Excel (geneste Vlookup)
Soms kan het gebeuren dat uw hoofdtabel en opzoektabel geen enkele kolom gemeen hebben, waardoor u geen Vlookup tussen twee tabellen kunt doen. Er bestaat echter een andere tabel, die niet de informatie bevat waarnaar u op zoek bent, maar een gemeenschappelijke kolom heeft met de hoofdtabel en een andere gemeenschappelijke kolom met de opzoektabel.
In onderstaande afbeelding wordt de situatie geïllustreerd:
Het doel is om prijzen naar de hoofdtabel te kopiëren op basis van artikel-IDs. Het probleem is dat de tabel met prijzen geen item-IDs heeft, wat betekent dat we twee Vlookups in één formule moeten doen.
Laten we voor het gemak eerst een paar benoemde bereiken maken :
- Opzoektabel 1 heet Producten (D3: E3).
- Opzoektabel 2 heet Prijzen (G3: H3).
De tabellen kunnen in dezelfde of verschillende werkbladen staan.
En nu gaan we de zogenaamde dubbele Vlookup uitvoeren, ook bekend als geneste Vlookup.
Maak eerst een VERT.ZOEKEN formule om de productnaam in de opzoektabel 1 (genaamd Products) te vinden op basis van de item-id (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Plaats vervolgens de bovenstaande formule in het argument lookup_value van een andere functie VERT.ZOEKEN om prijzen op te halen uit opzoektabel 2 (prijzen genaamd) op basis van de productnaam die wordt geretourneerd door de geneste VERT.ZOEKEN:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
De onderstaande schermafbeelding toont onze geneste Vlookup-formule in actie:
Hoe meerdere Vlookup werkbladen dynamisch
Soms kan het zijn dat u gegevens in hetzelfde formaat over meerdere werkbladen heeft verdeeld. En uw doel is om gegevens uit een specifiek blad te halen, afhankelijk van de sleutelwaarde in een bepaalde cel.
Dit is misschien gemakkelijker te begrijpen aan de hand van een voorbeeld. Stel dat u enkele regionale verkooprapporten heeft in dezelfde indeling en u wilt de verkoopcijfers voor een specifiek product in bepaalde regios opvragen:
Net als in het vorige voorbeeld beginnen we met het definiëren van een paar namen:
- Bereik A2: B5 in CA-blad heet CA_Sales.
- Bereik A2 : B5 in FL-blad heet FL_Sales.
- Bereik A2: B5 in KS-blad heet KS_Sales.
Zoals u kunt zien, hebben alle benoemde bereiken een gemeenschappelijke deel (verkoop) en unieke onderdelen (CA, FL, KS). Zorg ervoor dat u uw reeksen op dezelfde manier een naam geeft, aangezien dit essentieel is voor de formule die we gaan bouwen.
Formule 1. INDIRECTE VERT.ZOEKEN om dynamisch gegevens uit verschillende bladen op te halen
Als het uw taak is om gegevens uit meerdere bladen op te halen, is een VERT.ZOEKEN INDIRECTE formule de beste oplossing – compact en gemakkelijk te begrijpen.
Voor dit voorbeeld organiseren we de samenvattende tabel op deze manier:
- Voer de producten van belang in A2 en A3 in. Dat zijn onze opzoekwaarden.
- Voer de unieke delen van de genoemde bereiken in B1, C1 en D1 in.
En nu voegen we de cel met het unieke deel (B1) samen met het gemeenschappelijke deel (“_Sales”), en voeren de resulterende string naar INDIRECT:
INDIRECT(B$1&"_Sales")
De functie INDIRECT transformeert de tekenreeks in een naam die Excel kan begrijpen, en u plaatst deze in het argument table_array van VERT.ZOEKEN:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
De bovenstaande formule gaat naar B2, en dan kopieer je het naar beneden en naar rechts.
Let er alstublieft op dat, in de opzoekwaarde ($ A2) hebben we de kolomcoördinaat vergrendeld met absolute celverwijzing zodat de kolom vast blijft wanneer de formule naar rechts wordt gekopieerd. In de B $ 1-verwijzing hebben we de rij vergrendeld omdat we de kolom willen coördineren om te wijzigen en een geschikt naamgedeelte aan INDIRECT te leveren, afhankelijk van de kolom waarin de formule is gekopieerd:
Als uw hoofdtabel is georganiseerd anders, de opzoekwaarden in een rij en unieke delen van de bereiknamen in een colu mn, dan moet u de rijcoördinaat in de opzoekwaarde (B $ 1) en de kolomcoördinaat in de naamdelen ($ A2) vergrendelen:
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formule 2. VERT.ZOEKEN en geneste IFs om meerdere bladen op te zoeken
In een situatie waarin je net twee of drie opzoekbladen kunt u een vrij eenvoudige VERT.ZOEKEN-formule gebruiken met geneste ALS-functies om het juiste blad te selecteren op basis van de sleutelwaarde in een bepaalde cel:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Waar $ A2 de opzoekwaarde is (itemnaam) en B $ 1 de sleutelwaarde (staat):
In dit geval hoeft u niet per se namen te definiëren en kunt u externe verwijzingen gebruiken om naar een ander blad of een andere werkmap te verwijzen.
Voor meer formulevoorbeelden raadpleegt u VERT.ZOEKEN op meerdere bladen in Excel .
Zo gebruik je VERT.ZOEKEN in Excel. Ik dank u voor het lezen en hoop u volgende week op onze blog te zien!
Oefenwerkboek om te downloaden
Voorbeelden van geavanceerde VERT.ZOEKEN-formules (.xlsx-bestand)
- Excel VERT.ZOEKEN-zelfstudie voor beginners
- Hoe meerdere overeenkomsten in Excel opzoeken
- Hoe u VERT.ZOEKEN gebruikt & SOM- of SUMIF-functies in Excel
- INDEX & MATCH-functies in Excel – een beter alternatief voor VERT.ZOEKEN
- 4 manieren om een hoofdlettergevoelige Vlookup in Excel uit te voeren
- Excel VERT.ZOEKEN werkt niet – N / A, NAME en VALUE fouten