Leta upp närmaste matchning med INDEX & MATCH -funktionen

Anonim

I den här artikeln lär vi oss hur man letar upp närmaste matchning med hjälp av INDEX & MATCH -funktionen i Excel.

Scenario:

Till exempel måste vi hitta den numeriskt närmaste matchningen och dess motsvarande resultat från tabellen. Vi behöver en fast formel som hjälper till att hitta den närmaste matchningen för att få det önskade värdet.

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. MATCH -funktionen returnerar indexet för den närmaste matchningen från intervallet. INDEX -funktionen tar radindexet som argument och returnerar motsvarande erforderliga resultat.

Generisk formel:

{= INDEX (data, MATCH (MIN (ABS (data - värde)), ABS (data - värde), 0))}

data : array med datumvärden

värde : givet datumvärde där resultatet ligger närmast

match_type : 1 (exakt eller näst minsta) eller 0 (exakt matchning) eller -1 (exakt eller näst största)

LÅT BLI använd lockiga hängslen manuellt. Lockiga hängslen applicerade med Ctrl + Skift + Enter i stället för just Stiga på.

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 några slumpmässiga datumvärden och ett givet datumvärde.

Namngivet område som används för data (D4: D12).
Här använde vi det namngivna området istället för cellreferensmatris eftersom det är lätt att förstå. Vi måste leta efter det datum som är närmast det angivna datumet i G6 -cell.

Använd formeln ovan för att få det närmaste datumet.
Använd formeln:

{ = INDEX (data, MATCH (MIN (ABS (data - G6)), ABS (data - G6), 0)) }

ANVÄND INTE lockiga hängslen manuellt. Lockiga hängslen applicerade med Ctrl + Skift + Enter i stället för just Stiga på.

Förklaring:

  • ABS -funktionen returnerar det positiva heltalet efter subtraktionen som utförs över (data - G6) datumvärden.
  • ABS (data - G6) gör en array genom att subtrahera alla datumvärden från det angivna datumvärdet i G6 -cellen. Den returnerade matrisen är.

{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }

  • MIN (ABS (data - G6)) returnerar det närmaste värdet från matrisen som returneras från ovanstående steg.

= INDEX (data, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))

  • MATCH -funktionen matchar den närmaste minimivärdematchningen i den returnerade matrisen och returnerar sitt radindex till INDEX -funktionen.
  • INDEX -funktionen hittar värdet som har returnerat ROW -index.


data benämnt intervall som används för datummatrisen. Använd nu Ctrl + Skift + Enter på plats för just Enter -toppen får resultatet eftersom detta är en matrisformel. Arrayformel är en formel där en rad värden går från ett funktionsresultat som argument till yttre funktion.

Som du kan se från ovanstående ögonblicksbild fick vi den närmaste matchningen från det angivna datumet i tabellen. Extrahera detaljer från tabellen med ovanstående formel.

Här är några observationsnoter när du använder ovanstående formel.

Anmärkningar:

  1. Funktionen returnerar #NA -felet om uppslagsmatrisargumentet till MATCH -funktionen inte har samma längd som tabellmatrisen.
  2. Formeln returnerar ett fel om lookup_value inte matchar värdet i tabellen lookup_array.
  3. Funktionen matchar exakt värde eftersom matchningstypargumentet till MATCH -funktionen är 0.
  4. Uppslagsvärdena kan ges som cellreferens eller direkt med citatsymbol (") i formeln som argument.
  5. ANVÄND INTE lockiga hängslen manuellt. Lockiga hängslen applicerade med Ctrl + Skift + Enter i stället för just Stiga på annars returnerar det fel.

Hoppas du förstod hur man letar upp lägsta eller minsta matchning med hjälp av INDEX & MATCH -funktionen i excel. Utforska fler artiklar om Excel -sökvärde och Excel 2019 -funktioner här. Ange gärna dina frågor nedan i kommentarsfältet. Vi kommer säkert att hjälpa dig.

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

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.

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

Excel 2019 CONCAT -funktionen

Excel 2019 MAXIFS -funktionen