Varför INDEX och MATCH är bättre än VLOOKUP i Excel

Vi har tidigare förklarat för nybörjare hur man använder de grundläggande funktionerna i VLOOKUP (engelska VLOOKUP, förkortningen står för "vertical lookup function"). Och erfarna användare visades flera mer komplicerade formler.

Och i den här artikeln kommer vi att försöka ge information om en annan metod att arbeta med vertikal sökning.

Du kanske undrar: "Varför är detta nödvändigt?". Och detta är nödvändigt för att visa alla möjliga sökmetoder. Dessutom hindrar många VLOOKUP-restriktioner ofta att man uppnår det önskade resultatet. I detta avseende är INDEX( ) MATCH( ) mycket mer funktionell och mångsidig, och de har också färre begränsningar.

Grunder INDEX MATCH

Eftersom syftet med den här guiden är att visa hur bra den här funktionen är, har vi Låt oss titta på den grundläggande informationen om principerna för dess funktion. Och vi kommer att visa exempel, och också överväga varför, det är bättre än VLOOKUP ().

INDEX Funktionssyntax och användning

Denna funktion hjälper till att hitta det önskade värdet bland de angivna sökområdena baserat på kolumnen eller radnumret. Syntax:

=INDEX(matris, radnummer, kolumnnummer):

• array – området där sökningen kommer att äga rum;
• radnummer – numret på raden som ska sökas i den angivna arrayen. Om radnumret är okänt måste kolumnnumret anges;
• kolumnnummer – numret på den kolumn som ska hittas i den angivna arrayen. Om värdet är okänt krävs ett radnummer.

Ett exempel på en enkel formel:

=INDEX(A1:S10,2,3;XNUMX;XNUMX)

Funktionen söker i området från A1 till C10. Siffrorna visar vilken rad (2) och kolumn (3) som det önskade värdet ska visas från. Resultatet blir cell C2.

Ganska enkelt, eller hur? Men när du arbetar med riktiga dokument är det osannolikt att du har information om kolumnnummer eller celler. Det är vad MATCH()-funktionen är till för.

MATCH Funktion Syntax och användning

Funktionen MATCH() söker efter det önskade värdet och visar dess ungefärliga nummer i det angivna sökområdet.

Searchpos()-syntaxen ser ut så här:

=MATCH(värde att slå upp, array för att slå upp, matchningstyp)

• sökvärde – numret eller texten som ska hittas;
• genomsökt array – området där sökningen kommer att äga rum;
• matchningstyp – anger om man ska leta efter det exakta värdet eller de värden som ligger närmast det:
  • 1 (eller inget värde angivet) – returnerar det största värdet som är lika med eller mindre än det angivna värdet;
  • 0 – visar en exakt matchning med det sökta värdet. I kombinationen INDEX() MATCH() behöver du nästan alltid en exakt matchning, så vi skriver 0;
  • -1 – Visar det minsta värdet som är större än eller lika med värdet som anges i formeln. Sortering utförs i fallande ordning.

Till exempel, i intervallet B1:B3 är New York, Paris, London registrerade. Formeln nedan visar siffran 3 eftersom London är trea på listan:

=EXPONERA(London,B1:B3,0)

Hur man arbetar med INDEX MATCH-funktionen 

Du har antagligen redan börjat förstå principen med vilken det gemensamma arbetet för dessa funktioner är uppbyggt. Kort sagt alltså INDEX() söker efter det önskade värdet bland de angivna raderna och kolumnerna. Och MATCH() visar numren för dessa värden:

=INDEX(kolumn som värdet returneras från, MATCH(värde att söka, kolumn att söka i, 0))

Har du fortfarande svårt att förstå hur det fungerar? Kanske ett exempel förklarar bättre. Anta att du har en lista över världens huvudstäder och deras befolkning:

För att ta reda på storleken på befolkningen i en viss huvudstad, till exempel huvudstaden i Japan, använder vi följande formel:

=INDEX(C2:C10, MATCH(Japan, A2:A10,0))

Förklaring:

• Funktionen MATCH() letar efter värdet – “Japan” i arrayen A2:A10 och returnerar talet 3, eftersom Japan är det tredje värdet i listan. 
• Denna siffra går tilllinje nummer” i formeln INDEX() och talar om för funktionen att skriva ut ett värde från den här raden.

Så ovanstående formel blir standardformeln INDEX(C2:C10,3). Formeln söker från cellerna C2 till C10 och returnerar data från den tredje cellen i detta intervall, det vill säga C4, eftersom nedräkningen börjar från den andra raden.

Vill du inte skriva ut namnet på staden i formeln? Skriv det sedan i valfri cell, säg F1, och använd det som referens i MATCH()-formeln. Och du slutar med en dynamisk sökformel:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Viktigt! Antal rader in array INDEX() måste vara samma som antalet rader i betraktad array i MATCH(), annars får du fel resultat.

Vänta lite, varför inte bara använda formeln VLOOKUP()?

=SÖK UPP(F1; A2:C10; 3; Falskt)

 Vad är poängen med att slösa tid på att försöka lista ut alla dessa komplexiteter med INDEX MATCH?

I det här fallet spelar det ingen roll vilken funktion som ska användas. Detta är bara ett exempel för att förstå hur funktionerna INDEX() och MATCH() fungerar tillsammans. Andra exempel kommer att visa vad dessa funktioner är kapabla till i situationer där VLOOKUP är maktlös. 

INDEX MATCH eller VLOOKUP

När de bestämmer vilken sökformel som ska användas är många överens om att INDEX() och MATCH() är mycket överlägsna VLOOKUP. Men många använder fortfarande VLOOKUP(). För det första är VLOOKUP() enklare, och för det andra förstår användarna inte fullt ut alla fördelarna med att arbeta med INDEX() och MATCH(). Utan denna kunskap kommer ingen att gå med på att ägna sin tid åt att studera ett komplext system.

Här är de viktigaste fördelarna med INDEX() och MATCH() jämfört med VLOOKUP():

• Sök från höger till vänster. VLOOKUP() kan inte söka från höger till vänster, så värdena du letar efter måste alltid finnas i kolumnerna längst till vänster i tabellen. Men INDEX() och MATCH() kan hantera detta utan problem. Den här artikeln kommer att berätta hur det ser ut i praktiken: hur du hittar det önskade värdet på vänster sida.

1. Säker tillägg eller borttagning av kolonner. Formeln VLOOKUP() visar felaktiga resultat när du tar bort eller lägger till kolumner eftersom VLOOKUP() behöver det exakta kolumnnumret för att lyckas. När kolumner läggs till eller tas bort ändras naturligtvis även deras antal. 

Och i formlerna INDEX() och MATCH() anges ett intervall av kolumner, inte enskilda kolumner. Som ett resultat kan du säkert lägga till och ta bort kolumner utan att behöva uppdatera formeln varje gång.

1. Inga gränser för sökvolymer. När du använder VLOOKUP() får det totala antalet sökkriterier inte överstiga 255 tecken annars får du ett #VALUE! Så om din data innehåller ett stort antal tecken är INDEX() och MATCH() det bästa alternativet.
2. Hög bearbetningshastighet. Om dina bord är relativt små, är det osannolikt att du märker någon skillnad. Men om tabellen innehåller hundratals eller tusentals rader, och följaktligen det finns hundratals och tusentals formler, kommer INDEX () och MATCH () att klara sig mycket snabbare än VLOOKUP (). Faktum är att Excel kommer att bearbeta endast de kolumner som anges i formeln, istället för att bearbeta hela tabellen. 

Prestandapåverkan av VLOOKUP() kommer att vara särskilt märkbar om ditt kalkylblad innehåller ett stort antal formler som VLOOKUP() och SUM(). Separata kontroller av VLOOKUP()-funktionerna krävs för att analysera varje värde i en array. Så Excel måste bearbeta en enorm mängd information, och detta saktar ner arbetet avsevärt.

Formelexempel 

Vi har redan räknat ut användbarheten av dessa funktioner, så vi kan gå vidare till den mest intressanta delen: tillämpningen av kunskap i praktiken.

Formel för att söka från höger till vänster

Som redan nämnts kan VLOOKUP inte utföra denna form av sökning. Så om de önskade värdena inte finns i kolumnen längst till vänster, kommer VLOOKUP() inte att ge något resultat. Funktionerna INDEX() och MATCH() är mer mångsidiga, och placeringen av värdena spelar ingen stor roll för att de ska fungera.

Till exempel kommer vi att lägga till en rangkolumn till vänster i vår tabell och försöka ta reda på vilken rangordning i termer av befolkning vårt lands huvudstad upptar.

I cell G1 skriver vi värdet som ska hittas och använder sedan följande formel för att söka i intervallet C1:C10 och returnera motsvarande värde från A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Om du planerar att använda den här formeln för flera celler, se till att du fixar intervallen med absolut adressering (till exempel, $A$2: $A$10 och $C$2: 4C$10).

INDEX MER EXPONERAD MER EXPONERAD  för att söka i kolumner och rader

I exemplen ovan har vi använt dessa funktioner som en ersättning för VLOOKUP() för att returnera värden från ett fördefinierat radintervall. Men vad händer om du behöver göra en matris eller dubbelsidig sökning?

Det låter komplicerat, men formeln för sådana beräkningar liknar standardformeln INDEX() MATCH() med bara en skillnad: formeln MATCH() måste användas två gånger. Första gången för att få radnumret och andra gången för att få kolumnnumret:

=INDEX(matris, MATCH(vertikalt sökvärde, sökkolumn, 0), MATCH(horisontellt sökvärde, sökrad, 0))

Låt oss titta på tabellen nedan och försöka göra en formel INDEX() EXPRESS() EXPRESS() för att visa demografi i ett specifikt land för ett valt år.

Mållandet finns i cell G1 (vertikal uppslag) och målåret finns i cell G2 (horisontell uppslag). Formeln kommer att se ut så här:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Hur den här formeln fungerar

Som med alla andra komplexa formler är de lättare att förstå genom att dela upp dem i individuella ekvationer. Och då kan du förstå vad varje enskild funktion gör:

• MATCH(G1;A2:A11,0;XNUMX) – letar efter ett värde (G1) i intervallet A2:A11 och visar numret på detta värde, i vårt fall är det 2;
• SÖK(G2;B1:D1,0;XNUMX) – letar efter ett värde (G2) i området B1:D1. I det här fallet blev resultatet 3.

De hittade rad- och kolumnnumren skickas till motsvarande värde i INDEX()-formeln:

=INDEX(B2:D11,2,3;XNUMX;XNUMX)

Som ett resultat har vi ett värde som finns i en cell i skärningspunkten mellan 2 rader och 3 kolumner i intervallet B2:D11. Och formeln visar det önskade värdet, som finns i cell D3.

Sök efter flera villkor med INDEX och MATCH

Om du har läst vår guide till VLOOKUP() har du förmodligen provat flera sökformler. Men denna sökmetod har en betydande begränsning – behovet av att lägga till en extra kolumn.

Men de goda nyheterna är att Med INDEX() och MATCH() kan du söka efter flera villkor utan att behöva redigera eller ändra ditt kalkylblad.

Här är den allmänna sökformeln för flera villkor för INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Anteckningen: denna formel måste användas tillsammans med kortkommandot CTRL+SKIFT+ENTER.

Anta att du behöver hitta värdet du letar efter baserat på två villkor: Köparen и Produkt.

Detta kräver följande formel:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

I denna formel är C2:C10 intervallet inom vilket sökningen kommer att äga rum, F1 – detta tillstånd, A2:A10 — är intervallet för att jämföra tillståndet, F2 – villkor 2, V2: V10 – intervall för jämförelse av tillstånd 2.

Glöm inte att trycka på kombinationen i slutet av arbetet med formeln CTRL + SKIFT + RETUR – Excel stänger automatiskt formeln med hängslen, som visas i exemplet:

Om du inte vill använda en matrisformel för ditt arbete, lägg sedan till ytterligare ett INDEX() till formeln och tryck på RETUR, det kommer att se ut som i exemplet:

Hur dessa formler fungerar

Den här formeln fungerar på samma sätt som standardformeln INDEX() MATCH(). För att söka efter flera villkor skapar du helt enkelt flera falska och sanna villkor som representerar de korrekta och felaktiga individuella villkoren. Och då gäller dessa villkor för alla motsvarande element i arrayen. Formeln konverterar argumenten False och True till 0 respektive 1, och matar ut en array där 1 är de matchande värdena som hittades i strängen. MATCH() hittar det första värdet som matchar 1 och skickar det till INDEX()-formeln. Och det kommer i sin tur att returnera det redan önskade värdet i den angivna raden från den önskade kolumnen.

En formel utan en array beror på förmågan hos INDEX() att hantera dem på egen hand. Den andra INDEX() i formeln matchar falsk (0), så den skickar hela arrayen med dessa värden till MATCH()-formeln. 

Detta är en ganska lång förklaring av logiken bakom denna formel. För mer information läs artikeln "INDEXMATCH med flera villkor".

AVERAGE, MAX och MIN i INDEX och MATCH

Excel har sina egna specialfunktioner för att hitta medelvärden, maximum och minimum. Men vad händer om du vill få data från cellen som är associerad med dessa värden? I detta fall AVERAGE, MAX och MIN måste användas tillsammans med INDEX och MATCH.

INDEX MATCH och MAX

För att hitta det största värdet i kolumn D och visa det i kolumn C, använd formeln: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH och MIN

För att hitta det minsta värdet i kolumn D och visa det i kolumn C, använd följande formel:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SÖKINDEX och ORM

Så här hittar du medelvärdet i kolumn D och visar detta värde i C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Beroende på hur din data är skriven är det tredje argumentet till MATCH() antingen 1, 0 eller -1:

• om kolumnerna är sorterade i stigande ordning, ställ in 1 (då kommer formeln att beräkna det maximala värdet, som är mindre än eller lika med medelvärdet);
• om sorteringen är fallande, då -1 (formeln kommer att mata ut det lägsta värdet som är större än eller lika med genomsnittet);
• om uppslagsmatrisen innehåller ett värde som är exakt lika med medelvärdet, ställ in det till 0. 

 I vårt exempel är populationen sorterad i fallande ordning, så vi sätter -1. Och resultatet är Tokyo, eftersom befolkningsvärdet (13,189 000) är närmast medelvärdet (XNUMX).

VLOOKUP() kan också utföra sådana beräkningar, men bara som en matrisformel: VLOOKUP med AVERAGE, MIN och MAX.

INDEX MATCH och ESND/IFERROR

Du har förmodligen redan märkt att om formeln inte kan hitta det önskade värdet, ger den ett fel # N / A. Du kan ersätta standardfelmeddelandet med något mer informativt. Ange till exempel argumentet i formeln I den XNUMX:e:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Med denna formel, om du anger data som inte finns i tabellen, kommer formuläret att ge dig det angivna meddelandet.

Om du vill fånga alla fel, utom I den XNUMX:e kan användas FEL:

=FEL(INDEX(C2:C10,MATCH(F1;A2:A10,0)), "Något gick fel!")

Men kom ihåg att maskering av fel på detta sätt inte är en bra idé, eftersom standardfel rapporterar överträdelser i formeln.

Vi hoppas att du tyckte att vår guide till att använda funktionen INDEX MATCH() var till hjälp.