✅ Hitta den närmaste matchningen i Excel med hjälp av formler

Innehållsförteckning

Hitta den närmaste matchningen i Excel

Excel -funktioner kan vara extremt kraftfulla om du hänger på att kombinera olika formler. Saker som kan ha verkat omöjliga skulle plötsligt börja se ut som en barns lek.

Ett sådant exempel är att hitta den närmaste matchningen av ett uppslagsvärde i en datamängd i Excel.

Det finns ett par användbara sökfunktioner i Excel (som VLOOKUP & INDEX MATCH), som kan hitta den närmaste matchningen i några enkla fall (som jag kommer att visa med exempel nedan).

Men det bästa är att du kan kombinera dessa sökfunktioner med andra Excel -funktioner för att göra mycket mer (inklusive att hitta den närmaste matchningen av ett uppslagsvärde i en osorterad lista).

I den här självstudien visar jag dig hur du hittar den närmaste matchningen av ett uppslagsvärde i Excel med uppslagsformler.

Hitta den närmaste matchningen i Excel

Det kan finnas många olika scenarier där du behöver leta efter närmaste matchning (eller närmaste matchningsvärde).

Nedan följer de exempel jag kommer att täcka i den här artikeln:

Hitta provisionen baserat på försäljning
Hitta den bästa kandidaten (baserat på den närmaste erfarenheten)
Hitta nästa evenemangsdatum

Låt oss börja!

Klicka här för att ladda ner exempelfilen

Hitta provisionskurs (letar efter det närmaste försäljningsvärdet)

Anta att du har en datamängd som visas nedan där du vill hitta provisionen för all säljpersonal.

Provisionen tilldelas baserat på försäljningsvärdet. Och detta beräknas med hjälp av tabellen till höger.

Till exempel, om en säljare gör den totala försäljningen på 5000, är provisionen 0% och om han/hon gör den totala försäljningen på 15000 är provisionen 5%.

För att få provision, måste du hitta det närmaste försäljningsintervallet bara lägre än försäljningsvärdet. Till exempel, för 15000 försäljningsvärde, skulle provisionen vara 10 000 (vilket är 5%) och för försäljningsvärdet 25 000 skulle provisionen vara 20 000 (vilket är 7%).

För att hitta närmaste försäljningsvärde och få provision, kan du använda den ungefärliga matchningen i VLOOKUP.

Nedanstående formel skulle göra detta:

= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)

Observera att i den här formeln är det sista argumentet 1, som säger att formeln ska använda en ungefärlig sökning. Det betyder att formeln skulle gå igenom försäljningsvärdena i kolumn E och hitta värdet som bara är lägre än uppslagsvärdet.

Sedan kommer VLOOKUP -formeln att ge provisionskostnaden för detta värde.

Notera: För att detta ska fungera måste du ha data sorterade i stigande ordning.

Klicka här för att ladda ner exempelfilen

Hitta den bästa kandidaten (baserat på den närmaste erfarenheten)

I exemplet ovan måste data sorteras i stigande ordning. Men det kan finnas fall där data inte sorteras.

Så låt oss ta ett exempel och se hur vi kan hitta den närmaste matchningen i Excel med hjälp av en kombination av formler.

Nedan är en exempeldatauppsättning där jag behöver hitta det anställdas namn som har arbetserfarenheten närmast önskat värde. Det önskade värdet i detta fall om 2,5 år.

Observera att data inte är sorterade. Den närmaste upplevelsen kan också vara antingen mindre eller mer än ge -upplevelsen. Till exempel är 2 år och 3 år båda lika nära (skillnad på 0,5 år).

Nedan är formeln som ger oss resultatet:

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Tricket i denna formel är att ändra uppslagsmatrisen och uppslagsvärdet för att hitta den minsta erfarenhetsskillnaden i erforderliga och faktiska värden.

Låt oss först förstå hur du skulle göra det manuellt (och sedan förklarar jag hur denna formel fungerar).

När du gör detta manuellt går du igenom varje cell i kolumn B och hittar skillnaden i erfarenheten mellan vad som krävs och det som en person har. När du har alla skillnader hittar du den som är minst och hämtar namnet på den personen.

Detta är exakt vad vi gör med denna formel.

Låt mig förklara.

Sökvärdet i MATCH-formeln är MIN (ABS (D2-B2: B15)).

Denna del ger dig den minsta skillnaden mellan den givna erfarenheten (som är 2,5 år) och alla andra erfarenheter. I det här exemplet returnerar det 0,3

Observera att jag har använt ABS för att se till att jag letar efter det närmaste (vilket kan vara mer eller mindre än den givna erfarenheten).

Nu blir detta minimivärde vårt uppslagsvärde.

Lookup-arrayen i MATCH-funktionen är ABS (D2- $ B $ 2: $ B $ 15).

Detta ger oss en rad siffror från vilka 2,5 (erforderlig erfarenhet) har subtraherats.

Så nu har vi ett uppslagsvärde (0,3) och en uppslagsmatris ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

MATCH -funktionen hittar positionen 0,3 i denna array, vilket också är positionen för personens namn som har den närmaste erfarenheten.

Detta positionsnummer används sedan av INDEX -funktionen för att returnera personens namn.

Obs! Om det finns flera kandidater som har samma minsta erfarenhet kommer ovanstående formel att ge namnet på den första matchande medarbetaren.

Hitta datum för nästa evenemang

Detta är ett annat exempel där du kan använda uppslagsformler för att hitta nästa datum för en händelse baserat på det aktuella datumet.

Nedan finns datauppsättningen där jag har händelsens namn och händelsedatum.

Vad jag vill är namnet på nästa evenemang och datumet för det kommande evenemanget.

Nedan är formeln som kommer att ge det kommande evenemangets namn:

= INDEX ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Och nedanstående formel kommer att ge det kommande evenemangsdatumet:

= INDEX ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Låt mig förklara hur denna formel fungerar.

För att få händelsedatum letar MATCH -funktionen efter det aktuella datumet i kolumn B. I det här fallet letar vi inte efter en exakt matchning, utan en ungefärlig. Och därför är det sista argumentet för MATCH -funktionen 1 (som hittar det största värdet som är mindre än eller lika med uppslagsvärdet).

Så MATCH -funktionen skulle returnera positionen för cellen som har datumet som är mindre än eller lika med det aktuella datumet. Så nästa händelse, i det här fallet, skulle vara i nästa cell (eftersom listan är sorterad i stigande ordning).

Så för att få det kommande evenemangsdatumet, lägg bara till ett till cellpositionen som returneras av MATCH -funktionen, och det ger dig cellpositionen för nästa händelsedatum.

Detta värde ges sedan av INDEX -funktionen.

För att få händelsens namn används samma formel och intervallet i INDEX -funktionen ändras från kolumn B till kolumn A.

Klicka här för att ladda ner exempelfilen

Idén med detta exempel kom till mig när en vän närmade sig en begäran. Han hade en lista över alla sina vänner/släktingar födelsedagar i en kolumn och ville veta nästa födelsedag som kommer (och namnet på personen).

Det här är tre exempel som visar hur du hittar det närmaste matchningsvärdet i Excel med hjälp av uppslagsformler.

Du kanske också gillar följande Excel -tips/självstudier