I den här artikeln lär vi oss hur man letar upp värden i 2d-tabell med en INDEX-MATCH-MATCH-funktion i Excel.
Scenario:
Antag att du behöver göra flera sökningar från en tabell som har hundratals kolumner. I sådana fall tar det för lång tid att använda olika formler för varje uppslagning. Vad sägs om att skapa en dynamisk uppslagsformel som du kan slå upp med den angivna rubriken. Ja, vi kan göra det här. Denna formel kallas INDEX MATCH MATCH -formel, eller säg en 2d -uppslagsformel.
Hur löser man problemet?
För att formeln ska förstå först måste vi revidera lite om följande funktioner
- INDEX -funktion
- MATCH -funktion
INDEX -funktionen returnerar värdet vid ett givet index i en array.
MATCH -funktionen returnerar indexet för det första utseendet på värdet i en array (array med en dimension).
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ärdet2 i kolumnrubrikfältet. Indexnumren matas nu in i INDEX -funktionen för att få värdena under uppslagsvärdet från 2D -tabelldata.
Generisk formel:
= INDEX (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))
Data: mängd värden i tabellen utan rubriker
lookup_value1 : värde att slå upp i rad_huvudet.
row_headers : Radindex för att slå upp.
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.
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 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 som namngivna intervall som nämns i ögonblicksbilden ovan.
Som du kan se i ögonblicksbilden ovan har vi Priset erhållet efter höjd (34) & Bredd (21) som 53.10. Det bevisar att formeln fungerar bra och för tvivel se anteckningarna nedan för mer förståelse.
Anmärkningar:
- Funktionen returnerar #NA -felet om uppslagsmatrisargumentet till MATCH -funktionen är 2 D -matris som är datahuvudfältet …
- Funktionen matchar det exakta värdet eftersom matchningstypargumentet till MATCH -funktionen är 0.
- Uppslagsvärdena kan ges som cellreferens eller direkt med citatsymbol (") i formeln som argument.
Hoppas du förstod hur du använder Lookup in 2 D -tabellen med INDEX & MATCH -funktionen i Excel. Utforska fler artiklar i Excel -sökvärde här. Ange gärna dina frågor nedan i kommentarsfältet. Vi kommer säkert att hjälpa dig.
Använd INDEX och MATCH för att leta upp värde : INDEX & MATCH -funktion för att slå upp värdet efter behov.
SUM -intervall med INDEX i Excel : Använd INDEX -funktionen för att hitta SUMMAN för värdena efter behov.
Hur man använder SUM -funktionen i Excel : Hitta SUMMAN av siffror med SUM -funktionen som förklaras med exempel.
Så här använder du INDEX -funktionen i Excel : Hitta INDEX i matrisen med INDEX -funktionen som förklaras med exempel.
Hur man använder MATCH -funktionen i Excel : Hitta MATCH i matrisen med INDEX -värdet inuti MATCH -funktionen som förklaras med exempel.
Hur man använder LOOKUP -funktionen i Excel : Hitta uppslagsvärdet i arrayen med hjälp av LOOKUP -funktionen som förklaras med exempel.
Så här använder du funktionen VLOOKUP i Excel : Hitta uppslagsvärdet i matrisen med hjälp av VLOOKUP -funktionen som förklaras med exempel.
Så här använder du HLOOKUP -funktionen i Excel : Hitta uppslagsvärdet i matrisen med HLOOKUP -funktionen som förklaras med exempel.
Populära artiklar
50 Excel -genväg för att öka din produktivitet
Redigera en rullgardinslista
Absolut referens i Excel
Om med villkorlig formatering
Om med jokertecken
Vlookup efter datum
Gå med för- och efternamn i excel