Hitta den sista förekomsten av ett uppslagsvärde en lista i Excel

I den här självstudien lär du dig hur du hittar den sista förekomsten av ett objekt i en lista med hjälp av Excel -formler.

Nyligen arbetade jag med att sätta agendan för ett möte.

Jag hade en lista i Excel där jag hade en lista över personer och datum då de fungerade som "mötesordförande".

Eftersom det fanns upprepning i listan (vilket innebär att en person har varit mötesordförande flera gånger), behövde jag också veta när det var sista gången en person fungerade som "mötesordförande".

Detta var för att jag var tvungen att se till att någon som nyligen var ordförande inte tilldelades igen.

Så jag bestämde mig för att använda lite Excel Function -magi för att få detta gjort.

Nedan är det slutliga resultatet där jag kan välja ett namn från rullgardinsmenyn och det ger mig datumet för det senaste namnet i listan.

Om du har goda kunskaper om Excel -funktioner vet du att det inte finns någon Excel -funktion som kan göra detta.

Men du är i Formel Hack -sektionen, och här får vi det magiska att hända.

I den här självstudien visar jag dig tre sätt att göra detta.

Hitta den sista förekomsten - Använda MAX -funktionen

Tack till denna teknik går till en artikel av Excel MVP Charley Kyd.

Här är Excel -formeln som returnerar det sista värdet från listan:

= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (RAD ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))

Så här fungerar den här formeln:

• MAX -funktionen används för att hitta radnumret för det sista matchande namnet. Till exempel, om namnet är Glen, skulle det returnera 11, som det är på 11 -raden. Sedan vår lista börjar från andra raden och framåt har 1 subtraherats. Så positionen för den sista förekomsten av Glen är 10 på vår lista.
• SUMPRODUCT används för att säkerställa att du inte behöver använda Control + Shift + Enter, eftersom SUMPRODUCT kan hantera matrisformler.
• INDEX -funktionen används nu för att hitta datum för det sista matchande namnet.

Hitta den sista förekomsten - Använd LOOKUP -funktionen

Här är en annan formel för att göra samma jobb:

= LOOKUP (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)

Så här fungerar den här formeln:

• Sökvärdet är 2 (du kommer att se varför … fortsätt läsa)
• Letningsområdet är 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Detta returnerar 1 när det hittar matchande namn och ett fel när det inte gör det. Så du får en array. Till exempel, av uppslagsvärdet är Glen, skulle matrisen vara {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
• Det tredje argumentet ([result_vector]) är intervallet från vilket det ger resultatet, som är datum i det här fallet.

Anledningen till att denna formel fungerar är att LOOKUP -funktionen använder ungefärlig matchningsteknik. Detta betyder att om det kan hitta det exakta matchningsvärdet skulle det returnera det, men om det inte kan, kommer det att skanna hela arrayen till slutet och returnera det näst största värdet som är lägre än uppslagsvärdet.

I det här fallet är uppslagsvärdet 2, och i vår array får vi bara 1: or eller fel. Så det skannar hela arrayen och returnerar positionen för den sista 1 - vilket är det sista matchningsvärdet för namnet.

Hitta den sista förekomsten - Använda anpassad funktion (VBA)

Låt mig också visa dig ett annat sätt att göra detta.

Vi kan skapa en anpassad funktion (även kallad användardefinierad funktion) med VBA.

Fördelen med att skapa en anpassad funktion är att den är enkel att använda. Du behöver inte oroa dig för att skapa en komplex formel varje gång, eftersom det mesta av arbetet sker i VBA -backend.

Jag har skapat en enkel formel (som liknar VLOOKUP -formel mycket).

För att skapa en anpassad funktion måste du ha VBA -koden i VB Editor. Jag kommer att ge dig koden och stegen för att placera den i VB Editor om ett tag, men låt mig först visa dig hur det fungerar:

Detta är formeln som ger dig resultatet:

= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)

Formeln tar tre argument:

• Sökvärde (detta skulle vara namnet i cell D3)
• Lookup Range (detta skulle vara intervallet som har namn och datum - A2: B14)
• Kolumnummer (det här är den kolumn som vi vill ha resultatet från)

När du har skapat formeln och lagt koden i VB Editor kan du använda den precis som alla andra vanliga Excel -kalkylbladfunktioner.

Här är koden för formeln:

'Detta är en kod för en funktion som hittar den senaste förekomsten av ett uppslagsvärde och returnerar motsvarande värde från den angivna kolumnen' Kod skapad av Sumit Bansal (https://trumpexcel.com) Funktion LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns (1) .Cells.Count to 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Avsluta funktion Avsluta om nästa i avsluta funktion

Här är stegen för att placera denna kod i VB Editor:

1. Gå till fliken Utvecklare.
2. Klicka på alternativet Visual Basic. Detta öppnar VB -redigeraren i backend.
3. I fönstret Projektutforskare i VB-redigeraren högerklickar du på valfritt objekt för arbetsboken där du vill infoga koden. Om du inte ser Project Explorer går du till fliken Visa och klickar på Project Explorer.
4. Gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
5. Kopiera och klistra in koden i modulfönstret.

Nu skulle formeln vara tillgänglig i alla arbetsblad i arbetsboken.

Observera att du måste spara arbetsboken i .XLSM -format eftersom den har ett makro. Om du vill att den här formeln ska vara tillgänglig i alla arbetsböcker du använder kan du antingen spara den i den personliga makro-arbetsboken eller skapa ett tillägg från den.