Titta på video - Sök efter det andra, det tredje eller det n: a matchande värdet
När det gäller att leta upp data i Excel finns det två fantastiska funktioner som jag ofta använder - VLOOKUP och INDEX (mestadels i samband med MATCH -funktionen).
Dessa formler är dock utformade för att endast hitta den första instansen av uppslagsvärdet.
Men vad händer om du vill slå upp det andra, tredje, fjärde eller Nth-värdet.
Tja, det går att göra med lite extra arbete.
I den här självstudien kommer jag att visa dig olika sätt (med exempel) på hur du letar upp det andra eller det n: e värdet i Excel.
Leta upp det andra, tredje eller n: e värdet i Excel
I den här självstudien kommer jag att täcka två sätt att leta upp det andra eller det n: e värdet i Excel:
- Med hjälp av en hjälparpelare.
- Använda matrisformler.
Låt oss börja och dyka in direkt.
Använda hjälpkolumn
Anta att du är en utbildningskoordinator i en organisation och har en datamängd som visas nedan. Du vill lista all utbildning framför en anställds namn.
I ovanstående dataset har de anställda fått utbildning i olika Microsoft Office -verktyg (Excel, PowerPoint och Word).
Nu kan du använda VLOOKUP -funktionen eller kombinationen INDEX/MATCH för att hitta den utbildning en anställd har genomfört. Det returnerar dock bara den första matchande förekomsten.
Till exempel, i fallet med John, har han tagit alla tre träningarna, men när jag letar upp hans namn med VLOOKUP eller INDEX/MATCH kommer det alltid att returnera 'Excel', vilket är den första träningen för hans namn i listan .
För att få detta gjort kan vi använda en hjälparkolumn och skapa unika uppslagsvärden i den.
Här är stegen:
- Infoga en kolumn före kolumnen som visar utbildningen.
- Ange följande formel i cell B2:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- I cell F2 anger du följande formel och kopierar och klistrar in för alla andra celler:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Ovanstående formel skulle returnera utbildningen för varje anställd i den ordning den visas på listan. Om det inte finns någon utbildning för en anställd, returnerar den ett tomt.
Hur fungerar denna formel?
COUNTIF -formeln i hjälpkolumnen gör varje anställds namn unikt genom att lägga till ett nummer i det. Till exempel blir den första instansen av John John1, den andra instansen blir John2 och så vidare.
VLOOKUP -formeln använder nu dessa unika medarbetarnamn för att hitta matchande utbildning.
Observera att $ E2 & COLUMNS ($ F $ 1: F1) är uppslagsvärdet i formeln. Detta skulle lägga till ett nummer till medarbetarnamnet baserat på kolumnnumret. Till exempel, när denna formel används i cell F2, blir uppslagsvärdet "John1". I cell G2 blir det “John2” och så vidare.
Använda Array Formula
Om du inte vill ändra den ursprungliga datamängden genom att lägga till hjälparkolumner kan du också använda en matrisformel för att leta upp det andra, tredje eller n: a värdet.
Anta att du har samma datauppsättning som visas nedan:
Här är formeln som returnerar det korrekta uppslagsvärdet:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMÅ (IF ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ""), COLUMNS ($ E $ 1) : E1))), "")
Kopiera denna formel och klistra in den i cell E2.
Observera att detta är en matrisformel och att du måste använda Ctrl + Skift + Retur (håll ned Ctrl- och Shift -tangenterna och tryck på Enter) istället för att bara trycka på Enter.
Klicka här för att ladda ner exempelfilen.
Hur fungerar denna formel?
Låt oss dela upp denna formel i delar och se hur det fungerar.
$ A $ 2: $ A $ 14 = $ D2
Ovanstående del av formeln jämför varje cell i A2: A14 med värdet i D2. I denna dataset kontrollerar den om en cell innehåller namnet "John" eller inte.
Den returnerar en matris med TRUE of FALSE. Om cellen har namnet 'John' skulle det vara sant, annars skulle det vara falskt.
Nedan är matrisen du skulle få i detta exempel:
{SANT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT
Observera att den har SANT på 1: a, 7: e och 111: e plats, eftersom det är där namnet John visas i datamängden.
OM ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ””)
Ovanstående IF-formel använder matrisen TRUE och FALSE och ersätter TRUE med positionen för dess förekomst i listan (angiven med ROW ($ A $ 2: $ A $ 14) -1) och FALSE med “” (blank). Följande är den resulterande matrisen du får med denna IF -formel:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Notera än 1, 7 och 11 är positionen för John förekomst i listan.
LILLA (OM ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ”” ”), COLUMNS ($ E $ 1: E1))
SMALL -funktionen väljer nu det första minsta, näst minsta, tredje minsta antalet från denna array. Observera att den använder funktionen COLUMNS för att generera kolumnnumret. I cell E2 returnerar funktionen COLUMNS 1 och SMALL -funktionen 1. I cell F2 returnerar COLUMNS -funktionen 2 och SMALL -funktionen returnerar 7.
INDEX ($ B $ 2: $ B $ 14, SMÅ (OM ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ”” ”), COLUMNS ($ E $ 1: E1) )))
INDEX -funktionen returnerar nu värdet från listan i kolumn B baserat på positionen som returneras av funktionen SMALL. Därför returnerar den i cell E2 "Excel", vilket är det första objektet i B2: B14. I cell F2 returnerar den PowerPoint, som är det sjunde objektet i listan.
Eftersom det finns fall där det bara finns en eller två utbildningar för vissa anställda, skulle INDEX -funktionen returnera ett fel. IFERROR -funktionen används för att returnera ett tomt i stället för felet.
Observera att i dessa exempel har jag använt intervallreferenser. Men i praktiska exempel är det fördelaktigt att konvertera data till en Excel -tabell. Genom att konvertera till en Excel -tabell kan du använda strukturerade referenser, vilket gör det enklare att skapa formler. Dessutom kan en Excel -tabell automatiskt redogöra för alla nya träningsposter som läggs till i listan (så att du inte behöver justera formlerna varje gång).
Vad gör du när du måste leta upp det andra, tredje eller Nth-värdet? Jag är säker på att det finns fler sätt att göra detta. Om du använder något enklare än det som anges här, dela med oss alla i kommentarsfältet.
Klicka här för att ladda ner exempelfilen.
