Excels nya XLOOKUP kommer att ersätta VLOOKUP, vilket ger en kraftfull ersättning till en av Excels mest populära funktioner. Denna nya funktion löser några av VLOOKUPs begränsningar och har extra funktionalitet. Här är vad du behöver veta.
Innehållsförteckning
Vad är XLOOKUP?
Den nya XLOOKUP-funktionen har lösningar för några av de största begränsningarna i VLOOKUP. Dessutom ersätter den HLOOKUP. Till exempel kan XLOOKUP se till vänster, har som standard en exakt matchning och låter dig ange ett cellintervall istället för ett kolumnnummer. VLOOKUP är inte så lätt att använda eller lika mångsidigt. Vi visar dig hur det hela fungerar.
För närvarande är XLOOKUP endast tillgängligt för användare på Insiders-programmet. Vem som helst kan gå med i Insiders-programmet för att komma åt de senaste Excel-funktionerna så snart de blir tillgängliga. Microsoft kommer snart att börja rulla ut det till alla Office 365-användare.
Hur man använder XLOOKUP-funktionen
Låt oss dyka rakt in med ett exempel på XLOOKUP i aktion. Ta exemplet nedan. Vi vill returnera avdelningen från kolumn F för varje ID i kolumn A.
Detta är ett klassiskt exempel på exakt matchning. XLOOKUP-funktionen kräver bara tre delar av information.
Bilden nedan visar XLOOKUP med sex argument, men endast de tre första är nödvändiga för en exakt matchning. Så låt oss fokusera på dem:
Lookup_value: Vad du letar efter.
Lookup_array: Var ska man leta.
Return_array: intervallet som innehåller värdet som ska returneras.
Följande formel fungerar för det här exemplet: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Låt oss nu utforska ett par fördelar som XLOOKUP har jämfört med VLOOKUP här.
Inget mer kolumnindexnummer
Det ökända tredje argumentet för VLOOKUP var att ange kolumnnumret för informationen som skulle returneras från en tabelluppsättning. Detta är inte längre ett problem eftersom XLOOKUP gör att du kan välja intervallet att återvända från (kolumn F i det här exemplet).
Och glöm inte att XLOOKUP kan se data till vänster om den valda cellen, till skillnad från VLOOKUP. Mer om detta nedan.
Du har inte längre problemet med en trasig formel när nya kolumner infogas. Om det hände i ditt kalkylblad skulle returintervallet justeras automatiskt.
Exakt matchning är standard
Det var alltid förvirrande när man lärde sig VLOOKUP varför man var tvungen att ange en exakt matchning.
Lyckligtvis har XLOOKUP som standard en exakt matchning – den mycket vanligare anledningen till att använda en uppslagsformel). Detta minskar behovet av att svara på det femte argumentet och säkerställer färre misstag av användare som är nya i formeln.
Så kort sagt, XLOOKUP ställer färre frågor än VLOOKUP, är mer användarvänlig och är dessutom mer hållbar.
XLOOKUP kan titta till vänster
Att kunna välja ett uppslagsintervall gör XLOOKUP mer mångsidig än VLOOKUP. Med XLOOKUP spelar ordningen på tabellkolumnerna ingen roll.
VLOOKUP begränsades genom att söka i kolumnen längst till vänster i en tabell och sedan återvända från ett specificerat antal kolumner till höger.
I exemplet nedan måste vi slå upp ett ID (kolumn E) och returnera personens namn (kolumn D).
Följande formel kan uppnå detta: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Vad du ska göra om den inte hittas
Användare av uppslagsfunktioner är mycket bekanta med #N/A-felmeddelandet som hälsar dem när deras VLOOKUP eller deras MATCH-funktion inte kan hitta vad den behöver. Och ofta finns det en logisk anledning till detta.
Därför undersöker användare snabbt hur man döljer detta fel eftersom det inte är korrekt eller användbart. Och det finns naturligtvis sätt att göra det på.
XLOOKUP kommer med ett eget inbyggt ”om inte hittat”-argument för att hantera sådana fel. Låt oss se det i aktion med föregående exempel, men med ett felskrivet ID.
Följande formel visar texten ”Felaktigt ID” istället för felmeddelandet: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”Felaktig ID”)
Använda XLOOKUP för en Range Lookup
Även om det inte är lika vanligt som den exakta matchningen, är en mycket effektiv användning av en uppslagsformel att leta efter ett värde i intervall. Ta följande exempel. Vi vill återbetala rabatten beroende på det belopp som spenderats.
Den här gången letar vi inte efter ett specifikt värde. Vi måste veta var värdena i kolumn B faller inom intervallen i kolumn E. Det kommer att avgöra den intjänade rabatten.
XLOOKUP har ett valfritt femte argument (kom ihåg att det är standard på exakt matchning) som heter matchningsläge.
Du kan se att XLOOKUP har större möjligheter med ungefärliga matchningar än VLOOKUP.
Det finns möjlighet att hitta den närmaste matchningen som är mindre än (-1) eller närmast större än (1) det värde som sökts efter. Det finns också ett alternativ att använda jokertecken (2) som ? eller den *. Den här inställningen är inte på som standard som den var med VLOOKUP.
Formeln i det här exemplet returnerar det närmaste mindre än värdet som sökts efter om en exakt matchning inte hittas: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Det finns dock ett misstag i cell C7 där #N/A-felet returneras (argumentet ’om inte hittat’ användes inte). Detta borde ha returnerat en rabatt på 0 % eftersom utgifterna 64 inte når kriterierna för någon rabatt.
En annan fördel med XLOOKUP-funktionen är att den inte kräver att uppslagsintervallet är i stigande ordning som VLOOKUP gör.
Ange en ny rad längst ned i uppslagstabellen och öppna sedan formeln. Utöka det använda området genom att klicka och dra i hörnen.
Formeln korrigerar felet omedelbart. Det är inte ett problem att ha ”0” längst ner i intervallet.
Personligen skulle jag fortfarande sortera tabellen efter uppslagskolumnen. Att ha ”0” i botten skulle göra mig galen. Men det faktum att formeln inte gick sönder är lysande.
XLOOKUP Ersätter även HLOOKUP-funktionen
Som nämnts är XLOOKUP-funktionen också här för att ersätta HLOOKUP. En funktion för att ersätta två. Excellent!
HLOOKUP-funktionen är den horisontella uppslagningen, som används för att söka längs rader.
Inte lika känd som sin syskon VLOOKUP, men användbar för exempel som nedan där rubrikerna finns i kolumn A och data finns längs raderna 4 och 5.
XLOOKUP kan titta i båda riktningarna – nedåt i kolumner och även längs rader. Vi behöver inte längre två olika funktioner.
I det här exemplet används formeln för att returnera försäljningsvärdet för namnet i cell A2. Den letar längs rad 4 för att hitta namnet och returnerar värdet från rad 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP kan se nedifrån och upp
Vanligtvis måste du leta efter en lista för att hitta den första (ofta enda) förekomsten av ett värde. XLOOKUP har ett sjätte argument som heter sökläge. Detta gör det möjligt för oss att växla uppslagningen till att börja längst ner och slå upp en lista för att hitta den senaste förekomsten av ett värde istället.
I exemplet nedan vill vi hitta lagernivån för varje produkt i kolumn A.
Uppslagstabellen är i datumordning och det finns flera lagerkontroller per produkt. Vi vill returnera lagernivån från senaste gången den kontrollerades (senaste förekomsten av produkt-ID).
Det sjätte argumentet för XLOOKUP-funktionen ger fyra alternativ. Vi är intresserade av att använda alternativet ”Sök sist till först”.
Den färdiga formeln visas här: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
I denna formel ignorerades det fjärde och femte argumentet. Det är valfritt och vi ville ha en exakt matchning som standard.
Runda upp
XLOOKUP-funktionen är efterlängtad efterträdare till både SÖK UPP- och SLÖKNING-funktionerna.
En mängd olika exempel användes i den här artikeln för att visa fördelarna med XLOOKUP. En av dem är att XLOOKUP kan användas över ark, arbetsböcker och även med tabeller. Exemplen hölls enkla i artikeln för att hjälpa oss att förstå.
På grund av dynamiska arrayer som introduceras i Excel snart kan den också returnera en rad värden. Detta är definitivt något värt att utforska vidare.
Dagarna för VLOOKUP är räknade. XLOOKUP är här och kommer snart att vara de facto-uppslagsformeln.