Använd INDEX och MATCH -funktionen för att leta upp värde i Excel

Anonim

I den här artikeln kommer vi att lära oss hur du använder INDEX och MATCH -funktionen för att leta upp värde i Excel.

INDEX & MATCH -funktion för att ersätta vlookup -funktionen

Vi använder vanligtvis VLOOKUP -funktionen för att hitta ett värde som känner till raden Index och kolumnnummer. För VLOOKUP -funktionen måste vi ha kolumnnumret och radmatchningskriterierna och det viktigaste värdet för att ta reda på att det ligger till höger om matchningsvärdet. Men data stöder inte alltid det. Många gånger måste vi bara leta upp värden utan att känna till rad- eller kolumnindex, bara ha de värden som ska matchas. Till exempel: hitta lönen för en anställd som har anställnings -ID. I detta kommer vi att leta efter Medarbetar -ID för radindex och Lön för kolumnindex. Låt oss lära oss hur vi kan göra denna sökning med INDEX & MATCH.

Hur löser man problemet?

För att formeln ska förstå först måste vi revidera lite om följande funktioner

  1. INDEX -funktion
  2. MATCH -funktion

Nu kommer vi att göra en formel med hjälp av ovanstående funktioner. Matchningsfunktionen returnerar indexet för uppslagsvärdet1 i radhuvudfältet. Och en annan MATCH -funktion returnerar indexet för uppslagsvärdet 2 från kolumnrubrikfältet. Indexnumren matas nu in i INDEX -funktionen för att få värdena under uppslagsvärdet från tabelldata.

Generisk formel:

= INDEX (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))

data : array med värden i tabellen utan rubriker

lookup_value1 : värde att slå upp i rad_huvudet.

row_headers : Radindexmatris för sökning.

lookup_value1 : värde att slå upp i column_header.

column_headers : kolumn Indexmatris för sökning.

Exempel:

Ovanstående uttalanden kan vara komplicerade att förstå. Så låt oss förstå detta genom att använda formeln i ett exempel

Här har vi en lista över poäng som elever fått med deras ämneslista. Vi måste hitta poängen för en specifik student (Gary) och ämne (samhällskunskap) som visas i ögonblicksbilden nedan.

Studentvärdet1 måste matcha matrisen Row_header och Subject value2 måste matcha Column_header -arrayen.

Använd formeln i J6 -cellen:

= INDEX (tabell, MATCH (J5, rad, 0, MATCH (J4, kolumn, 0)))

Förklaring:

  • MATCH -funktionen matchar studentvärdet i J4 -cellen med radhuvudmatrisen och returnerar dess position 3 som ett nummer.
  • MATCH -funktionen matchar ämnesvärdet i J5 -cellen med kolumnrubriken och returnerar dess position 4 som ett nummer.
  • INDEX -funktionen tar rad- och kolumnindexnumret och letar upp i tabelldata och returnerar det matchade värdet.
  • MATCH -typargumentet är fastställt till 0. Eftersom formeln extraherar den exakta matchningen.

Här ges värden till formeln som cellreferenser och row_header, tabell och column_header anges som namngivna intervall.

Som du kan se i ögonblicksbilden ovan fick vi poängen som studenten erhöll Gary i ämne Samhällskunskap som 36. Och det bevisar att formeln fungerar bra och för tvivel se nedanstående anteckningar för förståelse.

Nu använder vi den ungefärliga matchningen med radrubriker och kolumnrubriker som siffror. Ungefär matchning tar bara talvärdena eftersom det inte finns något sätt att det gäller textvärden

Här har vi ett prisvärde enligt produktens höjd och bredd. Vi måste hitta priset för en specifik höjd (34) och bredd (21) som visas i ögonblicksbilden nedan.

Höjdvärdet1 måste matcha matrisen Row_header och Width value2 måste matcha Column_header -arrayen.

Använd formeln i K6 -cellen:

= INDEX (data, MATCH (K4, Höjd, 1, MATCH (K5, Bredd, 1)))

Förklaring:

  • MATCH -funktionen matchar höjdvärdet i K4 -cellen med radhuvudmatrisen och returnerar dess position 3 som ett nummer.
  • MATCH -funktionen matchar breddvärdet i K5 -cellen med kolumnrubriken och returnerar dess position 2 som ett nummer.
  • INDEX -funktionen tar rad- och kolumnindexnumret och letar upp i tabelldata och returnerar det matchade värdet.
  • Argumentet MATCH -typ är fixerat till 1. Eftersom formeln extraherar den ungefärliga matchningen.

Här ges värden till formeln som cellreferenser och row_header, data och column_header ges som namngivna intervall som nämns i ögonblicksbilden ovan.

Du kan också utföra exakta träffar med hjälp av INDEX och MATCH -funktionen i Excel. Läs mer om hur man gör skiftlägeskänslig sökning med hjälp av INDEX & MATCH -funktionen i Excel. Du kan också leta efter delmatchningarna med jokertecken i Excel.

Som du kan se i ögonblicksbilden ovan fick vi priset som höjd (34) & Bredd (21) som 53.10. Och det bevisar att formeln fungerar bra och för tvivel se nedanstående anteckningar för mer förståelse.

Anmärkningar:

  1. Funktionen returnerar #NA -felet om uppslagsmatrisargumentet till MATCH -funktionen är 2 D -matris som är datahuvudfältet …
  2. Funktionen matchar det exakta värdet eftersom matchningstypargumentet till MATCH -funktionen är 0.
  3. Uppslagsvärdena kan ges som cellreferens eller direkt med citatsymbol (") i formeln som argument.

Hoppas att den här artikeln om hur du använder Använd INDEX och MATCH -funktionen för att leta upp värde 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 den 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.

Använd INDEX och MATCH för att leta upp värde : INDEX-MATCH-formeln används för att leta dynamiskt och exakt ett värde i en given tabell. Detta är ett alternativ till VLOOKUP -funktionen och det övervinner bristerna i VLOOKUP -funktionen.

Använd VLOOKUP från två eller fler uppslagstabeller : För att leta upp från flera tabeller kan vi använda en IFERROR -metod. För att leta upp från flera tabeller tar det felet som en switch för nästa tabell. En annan metod kan vara en If -metod.

Hur man gör skiftlägeskänslig sökning i Excel : Excels VLOOKUP -funktion är inte skiftlägeskänslig och den returnerar det första matchade värdet från listan. INDEX-MATCH är inget undantag, men det kan ändras för att göra det skiftlägeskänsligt. Låt oss se hur …

Slå upp ofta text med kriterier i Excel : Sökningen visas oftast i text i ett område som vi använder INDEX-MATCH med MODE-funktionen. Här är metoden.

Populära artiklar:

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.

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 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.