Titta på video - Excel XLOOKUP -funktion (10 XLOOKUP -exempel)
Excel XLOOKUP -funktion har äntligen kommit.
Om du har använt VLOOKUP eller INDEX/MATCH är jag säker på att du kommer att älska flexibiliteten som XLOOKUP -funktionen ger.
I denna handledning kommer jag att täcka allt som finns att veta om XLOOKUP -funktionen och några exempel som hjälper dig att veta hur du bäst använder den.
Så låt oss komma igång!
Vad är XLOOKUP?
XLOOKUP är en ny funktion är Office 365 och är en ny och förbättrad version av funktionen VLOOKUP/HLOOKUP.
Den gör allt VLOOKUP brukade göra och mycket mer.
XLOOKUP är en funktion som låter dig snabbt leta efter ett värde i en datamängd (vertikal eller horisontell) och returnera motsvarande värde i någon annan rad/kolumn.
Om du till exempel har fått betyg för studenter i en tentamen kan du med XLOOKUP snabbt kontrollera hur mycket en elev har gjort med hjälp av studentens namn.
Kraften i den här funktionen kommer att bli ännu mer tydlig när jag dyker ner i några XLOOKUP -exempel senare i denna handledning.
Men innan jag går in på exemplen är det en stor fråga - hur får jag tillgång till XLOOKUP?
Hur får jag åtkomst till XLOOKUP?
Från och med nu är XLOOKUP endast tillgängligt för användare av Office 365.
Så om du använder tidigare versioner av Excel (2010/2013/2016/2019) kommer du inte att kunna använda den här funktionen.
Jag är inte heller säker på om detta någonsin skulle släppas för tidigare versioner eller inte (kanske Microsoft kan skapa ett tillägg som de gjorde för Power Query). Men från och med nu får du bara använda den om du använder Office 365.
Klicka här för att uppgradera till Office 365
Om du redan har Office 365 (hem-, personlig eller universitetsutgåva) och inte har åtkomst till det kan du gå till fliken Arkiv och sedan klicka på Konto.
Det skulle finnas ett Office Insider -program och du kan klicka och gå med i Office Insider -programmet. Detta ger dig tillgång till XLOOKUP -funktionen.
Jag förväntar mig att XLOOKUP snart är tillgängligt för alla Office 365 -versioner.
Obs! XLOOKUP är också tillgängligt för Office 365 för Mac och Excel för webben (Excel online)XLOOKUP Funktionssyntax
Nedan finns syntaxen för XLOOKUP -funktionen:
= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Om du har använt VLOOKUP kommer du att märka att syntaxen är ganska lika, med några fantastiska ytterligare funktioner förstås.
Oroa dig inte om syntaxen och argumentet ser lite för mycket ut. Jag täcker dessa med några enkla XLOOKUP -exempel senare i denna handledning som gör det kristallklart.XLOOKUP -funktionen kan berätta 6 argument (3 obligatoriska och 3 valfria):
- uppslagningsvärde - värdet du letar efter
- lookup_array - matrisen där du letar efter uppslagsvärdet
- return_array - matrisen från vilken du vill hämta och returnera värdet (motsvarande positionen där sökvärdet finns)
- [if_not_found] - värdet som ska returneras om uppslagsvärdet inte hittas. Om du inte anger detta argument returneras ett #N/A -fel
- [match_mode] - Här kan du ange vilken typ av matchning du vill ha:
- 0 - Exakt matchning, där lookup_value exakt ska matcha värdet i lookup_array. Detta är standardalternativet.
- -1 - Letar efter den exakta matchningen, men om den hittas returnerar nästa mindre artikel/värde
- 1 - Letar efter den exakta matchningen, men om den hittas returnerar nästa större artikel/värde
- 2 - För att göra delvis matchning med jokertecken (* eller ~)
- [sökmode] - Här anger du hur XLOOKUP -funktionen ska söka i lookup_array
- 1 - Detta är standardalternativet där funktionen börjar leta efter lookup_value från toppen (första objektet) till botten (sista objektet) i lookup_array
- -1 - Gör sökningen från botten till toppen. Användbart när du vill hitta det sista matchningsvärdet i lookup_array
- 2 - Utför en binär sökning där data måste sorteras i stigande ordning. Om det inte sorteras kan det ge fel eller felaktiga resultat
- -2 - Utför en binär sökning där data måste sorteras i fallande ordning. Om det inte sorteras kan det ge fel eller felaktiga resultat
XLOOKUP Funktionsexempel
Låt oss nu komma till den intressanta delen - några praktiska XLOOKUP -exempel.
Dessa exempel hjälper dig att bättre förstå hur XLOOKUP fungerar, hur det skiljer sig från VLOOKUP och INDEX/MATCH och några förbättringar och begränsningar av den här funktionen.
Klicka här för att ladda ner exempelfilen och följa med
Exempel 1: Hämta ett uppslagsvärde
Anta att du har följande datamängd och du vill hämta matematikpoängen för Greg (uppslagsvärdet).
Nedan är formeln som gör detta:
= XLOOKUP (F2, A2: A15, B2: B15)
I formeln ovan har jag precis använt de obligatoriska argumenten där det letar efter namnet (uppifrån och ner), hittar en exakt matchning och returnerar motsvarande värde från B2: B15.
En uppenbar skillnad som XLOOKUP- och VLOOKUP -funktionen har är hur de hanterar lookup -array. I VLOOKUP har du hela matrisen där uppslagsvärdet finns i kolumnen längst till vänster och sedan anger du kolumnnumret varifrån du vill hämta resultatet. XLOOKUP, å andra sidan, kan du välja lookup_array och return_array separatEn omedelbar fördel med att ha lookup_array och return_array som separata argument innebär att nu kan du titta till vänster. VLOOKUP hade denna begränsning där du bara kan slå upp och hitta ett värde som är till höger. Men med XLOOKUP är den begränsningen borta.
Här är ett exempel. Jag har samma dataset, där namnet är till höger och return_range är till vänster.
Nedan är formeln som jag kan använda för att få poängen för Greg i matematik (vilket innebär att titta till vänster om lookup_value)
= XLOOKUP (F2, D2: D15, A2: A15)
XLOOKUP löser ett annat stort problem - Om du sätter in en ny kolumn eller flyttar kolumner skulle den resulterande informationen fortfarande vara korrekt. VLOOKUP skulle troligen gå sönder eller ge ett felaktigt resultat i sådana fall eftersom kolumnindexvärdet oftast är hårdkodat.
Exempel 2: Leta upp och hämta en hel post
Låt oss ta samma data som ett exempel.
I det här fallet vill jag inte bara hämta Gregs poäng i matematik. Jag vill få poäng i alla ämnen.
I det här fallet kan jag använda följande formel:
= XLOOKUP (F2, A2: A15, B2: D15)
Ovanstående formel använder ett return_array -område som är mer än en kolumn (B2: D15). Så när uppslagsvärdet finns i A2: A15 returnerar formeln hela raden från return_array.
Du kan inte bara ta bort celler som ingår i matrisen som automatiskt fyllts i. I det här exemplet kan du inte ta bort H2 eller I2. Om du försöker skulle ingenting hända. Om du markerar dessa celler blir formeln i formelfältet nedtonad (vilket indikerar att den inte kan ändras)
Du kan ta bort formeln i cell G2 (där vi ursprungligen skrev in den), det kommer att ta bort hela resultatet.
Detta är en användbar förbättring som tidigare med VLOOKUP, du måste ange kolumnnumret separat för varje formel.
Exempel 3: Tvåvägsuppslagning med hjälp av XLOOKUP (horisontell och vertikal sökning)
Nedan finns en dataset där jag vill veta poängen för Greg i matematik (ämnet i cell G2).
Detta kan göras med hjälp av en tvåvägsuppslagning där jag letar efter namnet i kolumn A och ämnesnamnet i rad 1. Fördelen med denna tvåvägsuppslagning är att resultatet är oberoende av studentnamnet på ämnesnamnet. Om jag ändrar ämnesnamnet till kemi, skulle denna tvåvägs XLOOKUP-formel fortfarande fungera och ge mig rätt resultat.
Nedan är formeln som utför tvåvägsuppslagningen och ger rätt resultat:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
Denna formel använder en Nested XLOOKUP, där jag först använder den för att hämta alla märken för eleven i cell F2.
Så resultatet av XLOOKUP (F2, A2: A15, B2: D15) är {21,94,81}, vilket är en rad märken som görs av Greg i det här fallet.
Detta används sedan igen inom den yttre XLOOKUP -formeln som returmatris. I den yttre XLOOKUP -formeln letar jag efter ämnesnamnet (som finns i cell G1) och uppslagsmatrisen är B1: D1.
Om ämnesnamnet är matematik hämtar denna yttre XLOOKUP -formel det första värdet från returmatrisen - vilket är {21,94,81} i det här exemplet.
Detta gör detsamma som hittills uppnåddes med kombinationsfilen INDEX och MATCH
Klicka här för att ladda ner exempelfilen och följa med
Exempel 4: När uppslagningsvärde inte hittas (felhantering)
Felhantering har nu lagts till i XLOOKUP -formeln.
Det fjärde argumentet i XLOOKUP -funktionen är [if_not_found], där du kan ange vad du vill om uppslagningen inte kan hittas.
Anta att du har datauppsättningen som visas nedan där du vill få matematikpoängen om matchningen, och om namnet inte hittas, vill du returnera - "Visades inte"
Nedanstående formel kommer att göra detta:
= XLOOKUP (F2, A2: A15, B2: B15, "Visades inte")
I det här fallet har jag hårdkodat vad jag vill få om det inte finns någon match. Du kan också använda en cellreferens till en cell eller en formel.
Exempel 5: Kapslad XLOOKUP (sökning i flera områden)
Det geniala med att ha [if_not_found] -argumentet är att det låter dig använda kapslad XLOOKUP -formel.
Anta till exempel att du har två separata listor som visas nedan. Medan jag har dessa två tabeller på samma blad, kan du ha dessa i separata blad eller till och med arbetsböcker.
Nedan finns den kapslade XLOOKUP -formeln som söker efter namnet i båda tabellerna och returnerar motsvarande värde från den angivna kolumnen.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
I ovanstående formel har jag använt argumentet [if_not_found] för att använda en annan XLOOKUP -formel. Detta låter dig lägga till den andra XLOOKUP i samma formel och skanna två tabeller med en enda formel.
Jag är inte säker på hur många kapslade XLOOKUP du kan använda i en formel. Jag försökte till 10 och det fungerade, sedan gav jag upp 🙂
Exempel 6: Hitta det sista matchande värdet
Den här var välbehövlig och XLOOKUP gjorde detta möjligt. Nu behöver du inte hitta invecklade sätt att få det sista matchningsvärdet i ett intervall.
Anta att du har datauppsättningen enligt nedan och du vill kontrollera när den senaste personen anställdes på varje avdelning och vad som var hyrningsdatumet.
Nedanstående formel letar upp det sista värdet för varje avdelning och ger namnet på den senaste anställningen:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)
Och nedanstående formel kommer att ge hyrningsdatum för den senaste hyran för varje avdelning:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)
Eftersom XLOOKUP har en inbyggd funktion för att ange sökningens riktning (först till sista eller sista till första), görs detta med en enkel formel. Med vertikala data ser VLOOKUP och INDEX/MATCH alltid uppifrån och ner, men med XLOOKUP och kan också ange riktningen som botten till toppen.
Exempel 7: Ungefärlig matchning med XLOOKUP (Hitta skattesats)
En annan anmärkningsvärd förbättring med XLOOKUP är att det nu finns fyra matchlägen (VLOOKUP har 2 och MATCH har 3).
Du kan ange vilket som helst av de fyra argumenten för att avgöra hur uppslagsvärdet ska matchas:
- 0 - Exakt matchning, där lookup_value exakt ska matcha värdet i lookup_array. Detta är standardalternativet.
- -1 - Letar efter den exakta matchningen, men om den hittas returnerar nästa mindre artikel/värde
- 1 - Letar efter den exakta matchningen, men om den hittas returnerar nästa större artikel/värde
- 2 - För att göra delvis matchning med jokertecken (* eller ~)
Nedan har jag en dataset där jag vill hitta varje persons provision - och provisionen måste beräknas med hjälp av tabellen till höger.
Nedan är formeln som gör detta:
= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2
Detta använder helt enkelt försäljningsvärdet som uppslag och tittar genom uppslagstabellen till höger. I denna formel har jag använt -1 som det femte argumentet ([match_mode]), vilket innebär att det kommer att leta efter en exakt matchning, och när den inte hittar en, kommer den att returnera värdet bara mindre än uppslagsvärdet .
Och som sagt, du behöver inte oroa dig för om dina data inte är sorterade.
Klicka här för att ladda ner exempelfilen och följa med
Exempel 8: Horisontell sökning
XLOOKUP kan göra både vertikal och horisontell uppslagning.
Nedan har jag en datauppsättning där jag har elevnamn och deras poäng i rader, och jag vill hämta poängen för namnet i cell B7.
Nedanstående formel kommer att göra detta:
= XLOOKUP (B7, B1: O1, B2: O2)
Detta är inget annat än en enkel sökning (liknande det vi såg i exempel 1), men horisontellt.
Alla exempel som jag täcker om vertikal sökning kan också göras med en horisontell sökning med XLOOKUP (farväl till VLOOKUP och HLOOKUP).
Exempel 9: Villkorlig sökning (med XLOOKUP med andra formler)
Det här är ett något avancerat exempel och visar också kraften i XLOOKUP när du behöver göra komplexa sökningar.
Nedan finns en datauppsättning där jag har namnen på eleverna och deras poäng, och jag vill veta namnet på den elev som har fått högsta poäng i varje ämne och hur många elever som har gjort mer än 80 i varje ämne.
Nedan är formeln som ger namnet på eleven med de högsta betygen i varje ämne:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)
Eftersom XLOOKUP kan användas för att returnera en hel matris har jag använt den för att först få alla betyg för det obligatoriska ämnet.
Till exempel, för matematik, när jag använder XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), ger det mig alla poäng i matematik. Jag kan sedan använda MAX -funktionen för att hitta maximal poäng i detta intervall.
Denna maximala poäng blir då mitt uppslagsvärde, och uppslagningsintervallet är matrisen som returneras av XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Jag använder detta inom en annan XLOOKUP -formel för att hämta namnet på eleven som har fått högsta betyg.
Och för att räkna antalet elever som har fått mer än 80, använd följande formel:
= RÄTTA (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
Den här använder helt enkelt XLOOKUP -formeln för att få ett intervall av alla värden för det angivna ämnet. Den omsluter den sedan med COUNTIF -funktionen för att få antalet poäng som är mer än 80.
Exempel 10: Använda jokertecken i XLOOKUP
Precis som du kan använda jokertecken i VLOOKUP och MATCH, kan du också göra detta med XLOOKUP.
Men det är skillnad.
I XLOOKUP måste du ange att du använder jokertecken (i det femte argumentet). Om du inte anger detta kommer XLOOKUP att ge dig ett fel.
Nedan finns en datamängd där jag har företagsnamn och deras börsvärde.
Jag vill slå upp ett företagsnamn i kolumn D och hämta börsvärdet från tabellen till vänster. Och eftersom namnen i kolumn D inte är exakta matchningar måste jag använda jokertecken.
Nedan är formeln som gör detta:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
I formeln ovan har jag använt asterisk (*) jokertecken före som efter D2 (det måste ligga inom dubbla citattecken och förenas med D2 med hjälp av tecken).
Detta berättar formeln att titta igenom alla celler, och om det innehåller ordet i cell D2 (som är Apple), betrakta det som en exakt matchning. Oavsett hur många och vilka tecken som är före och efter texten i cell D2.
Och för att säkerställa att XLOOKUP accepterar jokertecken har det femte argumentet ställts in på 2 (jokerteckenmatchning).
Exempel 11: Hitta det sista värdet i kolumnen
Eftersom XLOOKUP låter dig söka från botten till toppen kan du enkelt hitta det sista värdet i en lista, samt hämta motsvarande värde från en kolumn.
Antag att du har en dataset som visas nedan och du vill veta vad som är det sista företaget och vad som är börsvärdet för det senaste företaget.
Nedanstående formel ger dig namnet på det sista företaget:
= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)
Och nedanstående formel ger marknadsvärdet för det sista företaget i listan:
= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)
Dessa formler använder igen jokertecken. I dessa har jag använt asterisk (*) som uppslagsvärde, vilket innebär att den första cellen som den möter skulle betraktas som en exakt matchning (eftersom asterisk kan vara valfritt tecken och valfritt antal tecken).
Och eftersom riktningen är från botten till toppen (för de vertikalt arrangerade data), kommer det att returnera det sista värdet i listan.
Och den andra formeln sedan använder en separat return_range för att få marknadsvärdet för efternamnet i listan.
Klicka här för att ladda ner exempelfilen och följa med
Vad händer om du inte har XLOOKUP?
Eftersom XLOOKUP sannolikt bara kommer att vara tillgängligt för Office 365 -användare är ett sätt att få det att uppgradera till Office 365.
Om du redan har Office 365 Home, Personal eller University edition har du redan tillgång till XLOOKUP. Allt du behöver göra är att gå med i Office Insider -programmet.
För att göra detta, gå till fliken Arkiv, klicka på Konto och klicka sedan på alternativet Office insider. Det skulle finnas ett alternativ att gå med i insider -programmet.
Om du har andra Office 365 -prenumerationer (till exempel Enterprise) är jag säker på att XLOOKUP och andra fantastiska funktioner (som dynamiska matriser, formler som SORT och FILTER) skulle bli tillgängliga snart.
Om du använder Excel 2010/2013/2016/2019 har du inte XLOOKUP, och du måste fortsätta använda VLOOKUP, HLOOKUP och INDEX/MATCH -kombinationen för att få ut det bästa av uppslagsformler.
XLOOKUP Bakåtkompatibilitet
Detta är en sak du måste vara försiktig med - XLOOKUP är INTE bakåtkompatibel.
Det betyder att om du skapar en fil och använder XLOOKUP -formeln och sedan öppnar den i en version som inte har XLOOKUP, visas fel.
Eftersom XLOOKUP är ett stort steg framåt i rätt riktning tror jag att detta kommer att bli standarduppslagningsformeln, men det kommer säkert att ta några år innan det blir allmänt antaget. När allt kommer omkring ser jag fortfarande vissa människor som använder Excel 2003.
Så det här är 11 XLOOKUP -exempel som kan hjälpa dig att göra alla sök- och referensgrejer som görs snabbare och också göra det enkelt att använda.
Hoppas du tyckte att denna handledning var användbar!
