I den här artikeln kommer vi att lära oss hur man hämtar ett pris från en lista som matchar både kategori och objektkriterier i Excel.
Scenario:
Det är lätt att hitta ett värde med ett kriterium i en tabell, för det kan vi helt enkelt använda VLOOKUP. Men när du inte har ett enda kolumnkriterium i din data och behöver hitta igenom flera kolumner för att matcha ett värde, hjälper VLOOKUP inte.
Generisk formel för både kategori och artikel
| = INDEX (lookup_range, MATCH (1, INDEX ((item1 = item_range) * (category2 = category_range), 0,1), 0)) |
lookup_range: Det är intervallet från vilket du vill hämta värdet.
Kriterier1, Kriterier2, Kriterier N: Dessa är de kriterier du vill matcha inom område1, intervall2 och intervall N. Du kan ha upp till 270 kriterier - intervallpar.
Område1, område2, intervallN: Dessa är de områden där du kommer att matcha dina respektive kriterier
Exempel:
Allt detta kan vara förvirrande att förstå. Låt oss förstå hur man använder funktionen med hjälp av ett exempel. Här har vi en datatabell. Jag vill dra kundens namn med bokningsdatum, byggare och område. Så här har jag tre kriterier och ett uppslagsområde.
Skriv denna formel i cell I4 tryck enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Vi vet redan hur INDEX och MATCH fungerar i EXCEL, så vi kommer inte att förklara det här. Vi kommer att prata om tricket vi använde här.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Huvuddelen är detta. Varje del av detta uttalande returnerar en grupp av true false.
När booleska värden multipliceras returnerar de en array med 0 och 1. Multiplikation fungerar som en OCH -operator. Hense när allt värde är sant först då returnerar det 1 annat 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Detta kommer helt tillbaka
Som kommer att översättas till
| {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 -funktionen returnerar samma array ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) till MATCH -funktion som uppslagningsarray.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktionen letar efter 1 i array {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Och returnerar indexnumret för de första 1 som finns i gruppen. Vilket är 8 här.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Slutligen returnerar INDEX värdet från det angivna intervallet (E2: E16) vid hittat index (8).
Om du kan slå CTRL + SKIFT + ENTER följaktligen kan du eliminera den inre INDEX -funktionen. Skriv bara den här formeln och tryck på CTRL + SKIFT + ENTER.
Formel
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Generisk matrisformel för flera kriterier
| = INDEX (lookup_range, MATCH (1, (kriterium1 = område1)*(kriterier2 = intervall2)*(kriterierN = intervallN), 0)) |
Här är alla observationsanteckningar med hjälp av formeln i Excel
Anmärkningar:
- Använd Vlookup om du har ett kriterium att matcha, det är vanlig praxis.
- Du kan lägga till fler rader och kolumner i uppslagsmatrisen.
- Textargument måste anges inom citattecken ("").
- VLOOKUP -tabellen måste ha lookup_array längst till vänster eller i den första kolumnen.
- Kol -index kan inte vara 1 eftersom det är uppslagsmatrisen
- 0 -argumentet används för det exakta matchningsvärdet. Använd 1 för det ungefärliga matchningsvärdet.
- Funktionen returnerar #N/A -fel, om uppslagningsvärdet inte hittas i uppslagsmatrisen. Så fånga felet, om det behövs.
Hoppas att den här artikeln om hur man hämtar ett pris från en lista som matchar både kategori och objektkriterier i Excel är förklarande. Hitta fler artiklar om beräkning av värden och relaterade Excel -formler här. Om du gillade våra bloggar, dela dem med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Så här hämtar du det senaste priset i Excel : Det är vanligt att uppdatera priser i alla företag och att använda de senaste priserna för alla köp eller försäljningar är ett måste. För att hämta det senaste priset från en lista i Excel använder vi LOOKUP -funktionen. LOOKUP -funktionen hämtar det senaste priset.
VLOOKUP -funktion för att beräkna betyg i Excel : Att beräkna betyg IF och IFS är inte de enda funktionerna du kan använda. VLOOKUP är mer effektiv och dynamisk för sådana villkorliga beräkningar. För att beräkna betyg med VLOOKUP kan vi använda denna formel.
17 saker om Excel VLOOKUP : VLOOKUP används oftast för att hämta matchade värden men VLOOKUP kan göra mycket mer än så. Här är 17 saker om VLOOKUP som du bör veta för att använda effektivt.
SÖK den första texten från en lista i Excel : VLOOKUP -funktionen fungerar bra med jokertecken. Vi kan använda detta för att extrahera det första textvärdet från en given lista i excel. Här är den generiska formeln.
LOOKUP -datum med sista värdet i listan : För att hämta datumet som innehåller det sista värdet använder vi LOOKUP -funktionen. Denna funktion söker efter cellen som innehåller det sista värdet i en vektor och använder sedan den referensen för att returnera datumet.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet : Bli snabbare på dina uppgifter i Excel. Dessa genvägar hjälper dig att öka din arbetseffektivitet i Excel.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.