Med VLOOKUP får vi alltid den första matchen. Detsamma händer med INDEX MATCH -funktionen. Så hur VLOOKUP andra matchen eller 3: e eller n: a? I den här artikeln kommer vi att lära oss hur man får den n: e förekomsten av ett värde i intervallet.
Generisk formel
{= LILLA (OM (intervall = värde,RAD(räckvidd)-RAD(first_cell_in_range)+1),n)}
Obs: detta är en matrisformel. Du måste ange den med CTRL + SKIFT + ENTER.
Räckvidd: intervallet där du vill söka nth position av värde.
Värde: värdet du letar efter nposition iräckvidd.
First_cell_in_range: den första cellen iräckvidd. Om intervallet är A2: A10 är den första cellen i intervallet A2.
n: de förekomst antal värden.
Låt oss se ett exempel för att göra saker tydliga.
Exempel: Hitta den andra matchningen i Excel
Så här har jag den här listan med namn i excelintervall A2: A10. Jag har namngett detta sortiment som namn. Nu vill jag få positionen för den andra förekomsten av "Rony" namn.
På bilden ovan kan vi se att den ligger på sjunde plats i intervall A2: A10 (namn). Nu måste vi få sin position med hjälp av en excelformel.
Tillämpa ovanstående generella formel i C2 för att leta upp den andra förekomsten av Rony i listan.
{= LILLA (OM (namn = "Rony" ,RAD(namn)-RAD(A2)+1),2)}
Ange det med CTRL + SKIFT + ENTER …
Och vi har ett svar. Det visar 7, vilket är korrekt. Om du ändrar värdet n till 3 ger det 8. Om du ändrar värdet på n större än förekomsten av värdet i intervallet returnerar det #NUM -fel.
Hur fungerar det?
Tja, det är ganska enkelt. Låt oss se varje del en efter en.
OM(namn = "Rony" ,RAD(namn)-RAD(A2)+1) :
I IF returnerar names = “Rony” en matris med SANT och FALSKT. SANT när en cell inom räckvidd namn (A2: A10) matchar "Rony". {SANT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT; SANT; SANT; FALSKT}.
Nästa RAD (namn)-RAD(A2)+1:
RAD(namn): här returnerar ROW -funktionen radnumret för varje cell i namn. {2; 3; 4; 5; 6; 7; 8; 9; 10}.
RAD(namn)-RAD(A2)Sedan subtraherar vi radnummer A2 från varje värde i den angivna matrisen. Detta ger oss en rad serienummer som börjar från 0. {0; 1; 2; 3; 4; 5; 6; 7; 8}.
RAD(namn)-RAD(A2)+1: För att få serienummer från 1, lägger vi till 1 till varje värde i denna array. Detta ger oss serienumret från 1. {1; 2; 3; 4; 5; 6; 7; 8; 9}.
Nu har vi IF ({SANT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT; SANT; FALSKT}, {1; 2; 3; 4; 5; 6; 7; 8; 9}). Detta löser sig till {1; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT; 7; 8; FALSKT}.
Nu har vi formeln löst till SMÅ ({1; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT;7;8;FALSK},2). Nu returnerar SMALL det näst minsta värdet i intervallet, vilket är 7.
Hur använder vi det?
Frågan kommer: vad är fördelen med att få ett råindex för den n: a matchen? Det skulle vara mer användbart om du kunde hämta relaterad information från n: e värdet. Det kan man också göra. Om vi vill få värde från den intilliggande cellens värde för n: e matchningen i intervallet namn (A2: A10).
{= INDEX (B2: B10, SMÅ (OM (namn = "Rony" ,RAD(namn)-RAD(A2)+1),2))}
Så ja grabbar, så här kan du få den n: e matchen i ett sortiment. Jag hoppas att jag var tillräckligt förklarande. Om du har några tvivel angående den här artikeln eller något annat Excel/VBA -relaterat ämne, skriv i kommentarfältet nedan.
Hur man får sekventiellt radnummer i Excel
Vlookup Topp 5 värden med dubblettvärden med INDEX-MATCH i Excel
VLOOKUP Flera värden
Använd INDEX och MATCH för att leta upp värde
Sökvärde med flera kriterier
Populära artiklar:
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Hur man använder SUMIF -funktionen i Excel