In dit artikel zullen we leren hoe u een prijs kunt ophalen uit een lijst die overeenkomt met zowel categorie- als artikelcriteria in Excel.
Scenario:
Het is gemakkelijk om een waarde met één criterium in een tabel te vinden, daarvoor kunnen we gewoon VERT.ZOEKEN gebruiken. Maar als u geen enkele kolomcriterium in uw gegevens heeft en meerdere kolommencriteria moet zoeken om aan een waarde te voldoen, helpt VERT.ZOEKEN niet.
Algemene formule voor zowel categorie als item
| =INDEX(lookup_range,MATCH(1,INDEX((item1=item_range) * (category2=category_range),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
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we eens kijken hoe we de functie kunnen gebruiken aan de hand van een voorbeeld. Hier hebben we 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)) |
We weten al hoe de INDEX- en MATCH-functie werken in EXCEL, dus dat gaan we 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 onderdeel is dit. Elk deel van deze instructie retourneert een array van true false.
Wanneer booleaanse waarden worden vermenigvuldigd, wordt een array van 0 en 1 geretourneerd. Vermenigvuldiging werkt als een AND-operator. Hense wanneer alle waarden alleen waar zijn, retourneert het 1 else 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Dit zal in totaal terugkeren
Wat zich vertaalt in
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((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.
MATCH(1,INDEX((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.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Ten slotte zal INDEX terugkeren de waarde uit het opgegeven bereik (E2:E16) bij gevonden index (8).
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.
Formule
| =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)) |
Hier zijn alle observatienotities met behulp van de formule in Excel
Opmerkingen:
- Gebruik Vlookup als u aan één criterium voldoet, het is gebruikelijk.
- U kunt meer rijen en kolommen toevoegen aan de opzoekarray.
- Tekstargumenten moeten tussen aanhalingstekens ("") staan.
- De tabel VERT.ZOEKEN moet de lookup_array in de meest linkse of de eerste kolom hebben.
- De col-index kan niet 1 zijn, omdat dit de opzoekarray is
- Het argument 0 wordt gebruikt voor de exacte overeenkomstwaarde. Gebruik 1 voor de geschatte overeenkomstwaarde.
- De functie retourneert de fout #N/A als de opzoekwaarde niet wordt gevonden in de opzoekarray. Dus vang de fout, indien nodig.
Ik hoop dat dit artikel over het ophalen van een prijs uit een lijst die overeenkomt met zowel categorie- als artikelcriteria in Excel verklarend is. Vind hier meer artikelen over het berekenen van waarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel ze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Hoe de laatste prijs in Excel op te halen : Het is gebruikelijk om de prijzen in elk bedrijf bij te werken en het is een must om de laatste prijzen te gebruiken voor elke aankoop of verkoop. Om de laatste prijs op te halen uit een lijst in Excel gebruiken we de LOOKUP functie. De LOOKUP-functie haalt de laatste prijs op.
VERT.ZOEKEN-functie om cijfer in Excel te berekenen : Om cijfers te berekenen zijn IF en IFS niet de enige functies die u kunt gebruiken. De VERT.ZOEKEN is efficiënter en dynamischer voor dergelijke voorwaardelijke berekeningen. Om cijfers te berekenen met VERT.ZOEKEN kunnen we deze formule gebruiken.
17 dingen over Excel VERT.ZOEKEN : VERT.ZOEKEN wordt meestal gebruikt voor het ophalen van overeenkomende waarden, maar VERT.ZOEKEN kan veel meer dan dit. Hier zijn 17 dingen over VERT.ZOEKEN die u moet weten om effectief te gebruiken.
ZOEK de eerste tekst uit een lijst in Excel : De functie VERT.ZOEKEN werkt prima met jokertekens. We kunnen dit gebruiken om de eerste tekstwaarde uit een bepaalde lijst in Excel te extraheren. Hier is de generieke formule.
LOOKUP-datum met laatste waarde in lijst : Om de datum op te halen die de laatste waarde bevat, gebruiken we de LOOKUP-functie. Deze functie zoekt naar de cel die de laatste waarde in een vector bevat en gebruikt vervolgens die verwijzing om de datum te retourneren.
Populaire artikels :
50 Excel-snelkoppelingen om uw productiviteit te verhogen : Werk sneller aan uw taken in Excel. Met deze sneltoetsen kunt u uw werkefficiëntie in Excel vergroten.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.