XLOOKUP(): Excels mest mångsidiga uppslagsfunktion

Om du någon gång har använt VLOOKUP() och kört fast för att du behövde söka åt vänster, löser XLOOKUP() det problemet. Det är Excels nyare uppslagsfunktion. Den kan ersätta VLOOKUP(), HLOOKUP() och till och med de flesta INDEX()- och MATCH()-formler med en enkel formel.

XLOOKUP() kan söka i alla riktningar, hämta mer än en kolumn åt gången och visa ett anpassat meddelande när ett värde saknas. 

I den här guiden går vi först igenom syntaxen. Sedan bygger vi riktiga formler steg för steg med ett enkelt dataset hela vägen. Vi går också igenom exakta och ungefärliga träffar, hur du returnerar mer än en kolumn, hur du hanterar fel och hur XLOOKUP() står sig mot VLOOKUP() och INDEX()/MATCH(). 

Så använder du XLOOKUP() i Excel

Låt oss bygga en riktig XLOOKUP()-formel från grunden. 

Vårt dataset har fem produkter, var och en med ett ID, ett namn, en kategori och ett pris, från kolumn A till D, med rubriker i rad 1.

Dataset för XLOOKUP(). Bild av författaren.

Säg att vi vill hitta priset för "P104". Vi går igenom hur man gör steg för steg.

Slå upp ett enskilt värde

Klicka i en tom cell där du vill att resultatet ska visas. Jag använder F2. Och skriv följande formel:

=XLOOKUP("P104", A2:A6, D2:D6)

I den här formeln: 

• "P104" är lookup_value, det vi sökte efter

• A2:A6 är lookup_array, området Excel sökte i

• D2:D6 är return_array, området Excel hämtade svaret från

Excel söker i kolumn A, ”Product ID”, efter "P104", hittar det i rad 5 och returnerar motsvarande värde från kolumn D: $45.

I stället för att skriva "P104" direkt kan du referera till en cell, så att du kan ändra uppslagsvärdet utan att redigera själva formeln. I vårt fall innehåller cell A5 "P104", så formeln blir:

=XLOOKUP(A5, A2:A6, D2:D6)

Använd XLOOKUP() i Excel. Bild av författaren.

Nu kan du skriva vilket produkt-ID som helst i A5, och priset uppdateras automatiskt.

Returnera ett värde från en annan kolumn

Detta steg visar den verkliga skillnaden mellan XLOOKUP() och VLOOKUP(). Du är inte låst till att returnera ett värde från en kolumn till höger om din sökkolumn.

Säg att du i stället för pris vill ha kategorin för "P102". Samma upplägg, peka bara return_array mot kolumn C, ”Category”, i stället:

=XLOOKUP(A3, A2:A6, C2:C6)

Här returnerar A3 Home. Om det ändras till A5 uppdateras det till Electronics. På samma sätt, ändra return_array igen för att hämta produktnamn, pris eller någon annan kolumn, allt med samma lookup_value och lookup_array.

XLOOKUP() returnerar värde från annan kolumn. Bild av författaren.

Med VLOOKUP() skulle du räkna kolumner från din sökkolumn och justera ett kolumnindexnummer varje gång du ville ha ett annat resultat. Med XLOOKUP() pekar du bara direkt på kolumnen du vill ha. 

Kopiera formeln över flera rader

Hittills har vi slagit upp ett värde i taget. Men de flesta verkliga kalkylblad behöver slå upp många värden samtidigt, säg en hel lista med order-ID:n som var och en behöver ett matchande pris.

Så här sätter du upp det: Kolumn A, med start i A2, innehåller redan vår lista med produkt-ID:n. Vi vill att de matchande priserna ska visas bredvid varje, i kolumn F.

För att göra det, klicka i F2 och skriv formeln, med referens till A2 som uppslagsvärde så här:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Klicka nu på F2 igen och dra sedan fyllningshandtaget (den lilla fyrkanten i cellens nedre högra hörn) ned genom resten av raderna.

Excel kopierar formeln nedåt, och varje rad returnerar nu rätt pris för sitt eget produkt-ID. A2:s ID får sitt pris i F2, A3:s ID får sitt pris i F3, och så vidare.

Kopiera formeln ned till andra celler. Bild av författaren.

Observera dollartecknen runt A2:A6 och D2:D6. Detta kallas en absolut referens. Den låser området så att det inte förskjuts när du kopierar formeln nedåt. 

Det spelar roll här, eftersom varje rad behöver söka i samma produktlista, bara med ett annat uppslagsvärde varje gång.

Så använder du exakt och ungefärlig träff i XLOOKUP()

Som standard letar XLOOKUP() efter en exakt träff. Om den inte hittar någon returnerar den ett fel, såvida du inte har ställt in argumentet if_not_found som vi täckte tidigare. 

Men XLOOKUP() kan också hitta närmaste träff, med hjälp av argumentet match_mode. 

Exakt träff (standard)

Du behöver inte skriva något extra för detta. Varje formel vi har byggt hittills använder redan exakt träff, eftersom det är XLOOKUP():s standardbeteende.

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel söker efter det exakta värdet i A2 och returnerar bara ett resultat om den hittar just den exakta träffen någonstans i uppslagsområdet.

Om du vill skriva ut det uttryckligen, lägg till en nolla som det fjärde argumentet:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() returnerar en exakt träff. Bild av författaren.

Lägg märke till mellanslaget före 0? Det hoppar över if_not_found, eftersom vi lämnar det tomt här, och går direkt till match_mode. För det mesta kan du utelämna detta argument helt och låta Excel själv använda exakt träff som standard.

Ungefärlig träff

Anta nu att dina data inte har ett värde för varje möjligt uppslag. Tänk på en provisionsstruktur där säljare tjänar olika nivåer beroende på vilket intervall deras totala försäljning hamnar i. Du kommer inte att ha en rad för varje dollarnivå, bara startpunkten för varje intervall.

Det är här en ungefärlig träff hjälper. 

Sätt match_mode till -1, så hittar XLOOKUP() det största värde som är mindre än eller lika med ditt uppslagsvärde.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Anta att ditt uppslagsområde innehåller försäljningströsklar på $0, $5 000 och $10 000, var och en kopplad till en annan provisionsnivå. 

En säljare med $7 500 i försäljning matchar inte någon rad exakt. Med match_mode satt till -1 hittar XLOOKUP() $5 000, den största tröskeln som inte överstiger $7 500, och returnerar den radens provisionsnivå.

Sätt match_mode till 1 i stället, så vänder XLOOKUP() logiken. Den hittar det minsta värde som är större än eller lika med ditt uppslagsvärde. Detta är användbart för sådant som fraktviktsintervall, där du rundar upp till nästa nivå i stället för ned.

XLOOKUP() returnerar en ungefärlig träff. Bild av författaren.

Obs: ungefärlig träff förutsätter att ditt uppslagsområde är sorterat, stigande för -1, fallande för 1. Om det inte är rätt sorterat kan XLOOKUP() returnera fel resultat utan att ge felmeddelande, vilket är värre. Så dubbelkolla alltid sorteringsordningen innan du förlitar dig på detta.

Så slår du upp värden åt vänster med XLOOKUP()

VLOOKUP() söker bara i den vänstra kolumnen i ditt område och returnerar ett värde från en kolumn till höger; den kan inte gå åt andra hållet. Dess tredje argument är inte ett område alls; det är ett kolumnindex räknat från vänsterkanten. 

Det finns inget sätt att säga ”gå en kolumn åt vänster”, så om värdet du behöver ligger till vänster om din sökkolumn sitter du fast med att arrangera om dina data eller lägga till en hjälpkolumn bara för att få uppslaget att fungera.

XLOOKUP() har inte den begränsningen, eftersom du pekar ut separata områden för sök och retur.

Ta vår produktlista: Product ID finns i kolumn A, Product Name i kolumn B. 

Anta att du har ett produktnamn och vill hitta dess ID; motsatsen till vad VLOOKUP() kan göra.

I C9 skriver du Bluetooth Speaker (ett namn som finns i listan). I E2 skriver du:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() söker i kolumn B efter "Bluetooth Speaker", hittar det i rad 5 och returnerar "P104" från kolumn A, även om kolumn A ligger till vänster om kolumn B. 

XLOOKUP() söker värden åt vänster, till skillnad från VLOOKUP(). Bild av författaren.

Så returnerar du flera kolumner med XLOOKUP()

XLOOKUP() kan returnera flera kolumner på en gång, i en enda formel, med en funktion som kallas dynamiska matriser. För att göra det pekar du return_array mot ett område i stället för en enskild kolumn, och XLOOKUP() spiller ut hela den matchande raden över så många celler som behövs. 

Låt oss prova med vår produktlista. 

I C9 skriver jag ett produkt-ID, till exempel "P104". I E2 skriver jag:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Observera att return_array här är B2:D6, tre kolumner brett, och täcker Product Name, Category och Price. När du trycker på Enter hittar XLOOKUP() "P104" i kolumn A och returnerar sedan Bluetooth Speaker, Electronics och $45 på en gång, spillt till E9, F9 och G9.

XLOOKUP() returnerar flera kolumner med en formel. Bild av författaren.

Detta är vad spillområde betyder. E9 innehåller den faktiska formeln, och F9 och G9 fylls i av sig själva, utan egna formler. 

Klicka på F9, så visar formelfältet en utgråad version av originalformeln, inte något som skrivits där direkt. Försök skriva i F9 medan E9:s spill är aktivt, och Excel ger ett spillfel. 

Vi går igenom hur du åtgärdar det i avsnittet Vanliga fel.

Så hanterar du saknade värden i XLOOKUP()

Som standard ger XLOOKUP() felet #N/A när den inte kan hitta en träff. Argumentet if_not_found kan lösa detta genom att visa det vi vill se i stället för ett standardfel.  

Anta att jag i C9 skriver ett produkt-ID som inte finns i mina data, som "P999".

Returnera ett anpassat meddelande

I G2 skriver jag:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Eftersom "P999" inte finns i kolumn A hoppar XLOOKUP() över felet helt och returnerar "Not Found". Ändra frasen till vad du vill, som "Check Product ID" eller "No Match".

Visa ett anpassat meddelande i stället för standardfelet. Bild av författaren.

Returnera en tom cell

Ibland vill du ha en tom cell utan text. För detta använder du två citattecken utan något emellan, så här:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Detta kan du använda när du använder formeln i en rapport där ett överblivet "Not Found" skulle se rörigt ut bredvid en kolumn med riktiga värden.

Visa en tom cell i stället för standardfelet. Bild av författaren.

Låta det vara ett genuint fel

Om du hoppar över if_not_found helt, faller XLOOKUP() tillbaka till #N/A. Detta är inte alltid dåligt. Om du bygger en formel för eget bruk och vill fånga en felskrivning eller en saknad post direkt, är det mer hjälpsamt att se #N/A dyka upp än en tyst tom cell som döljer problemet.

Obs: if_not_found fångar bara saknade träffar. Om dina lookup_array och return_array har olika storlek, till exempel, ger XLOOKUP() ändå felet #VALUE!, och if_not_found stoppar inte det. Vi täcker just det fallet i avsnittet Vanliga fel längre ned.

Avancerade funktioner i XLOOKUP()

Låt oss titta på de avancerade funktionerna i XLOOKUP()

Sök från botten till toppen

Sätt search_mode till -1, så skannar XLOOKUP() från botten av ditt område uppåt, i stället för uppifrån och ned. Använd detta när dina data har dubbletter och du vill ha den senaste posten, inte den första.

Säg att jag i mitt dataset har tre dubbletter av Customer ID. I cell E2 skriver jag standardformeln som använder tillvägagångssättet uppifrån och ned: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Detta beräknas från början av tabellen och visar resultatet från den första träffen. 

Men om jag vill söka från botten använder jag denna formel: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

I den här formeln talar -1 om för Excel att börja söka från botten av datasetet. Och detta returnerar 340, eftersom det är det första matchningsvärdet från botten.  

XLOOKUP() skannar data från botten till toppen. Bild av författaren.

Binärt sökläge

Sätt search_mode till 2, så byter XLOOKUP() från att kontrollera en rad i taget till ett binärt sök i stället. 

Så här fungerar det: 

• Det tittar först på mittenobjektet i ditt område. 

• Om det inte är en träff kastar det bort den halva där värdet inte kan finnas, och tittar sedan på mitten av det som återstår. 

• Det fortsätter så, och halverar datan varje gång, tills det hittar träffen. 

• Sätt search_mode till -2, så fungerar det likadant, bara att det börjar från sista objektet i stället för det första.

Här i E2, skriv:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Denna formel returnerar i stället 200, en helt annan rad.

Binärt sökläge ger fel resultat. Bild av författaren.

Det beror på att binärt sök bara fungerar om ditt lookup_array är sorterat. Eftersom vår kolumn Customer ID inte är sorterad, det står C100, C101, C100, C102, C100, kastar det binära söket bort fel halva och ger en felaktig träff. 

Låt oss nu se vad som händer när datan faktiskt är sorterad. 

Sortera samma logg efter Customer ID, stigande (alla tre C100 i början) och kör samma formel igen. 

Binärt sökläge fungerar med sorterade data. Bild av författaren.

Denna gång returnerar E2 120, i linje med vad en normal sökning skulle ge. Det är skillnaden. Binärt sök hoppar bara över data som säkert kan uteslutas, och det gäller bara när datan är sorterad från början. 

Nästa XLOOKUP()-formler

Nästa en XLOOKUP() inuti en annan när resultatet av ett uppslag behöver användas i ett andra uppslag, i stället för ett fast område.

Säg att du vill hitta ett pris, men vilken kolumn du hämtar det från beror på ett separat uppslag, till exempel en kunds prissättningstier.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

I den här formeln räknar den inre XLOOKUP() först ut vilket kolumnområde som ska hämtas från, baserat på D9, och sedan använder den yttre XLOOKUP() det resultatet som sitt return_array. 

Nästa XLOOKUP(). Bild av författaren.

XLOOKUP() vs. VLOOKUP()

Vi har gått igenom hur XLOOKUP() fungerar i detalj. Här är jämförelsen mot VLOOKUP().

Det finns två saker som är värda en snabb notering: 

• VLOOKUP() använder ungefärlig matchning som standard. Om du glömmer det fjärde argumentet visar den inte ett fel; den returnerar bara fel värde i tysthet.

• Om du infogar en kolumn i ett VLOOKUP()-ark börjar formler i tysthet hämta fel värde. XLOOKUP() undviker detta genom att referera till områden direkt i stället för att räkna kolumner.

Inget av detta gör VLOOKUP() föråldrat. Det fungerar fortfarande bra för enkla, statiska blad. Men för allt som byggs framöver är XLOOKUP() det säkrare förstahandsvalet.

XLOOKUP() vs. INDEX() och MATCH()

Innan XLOOKUP() fanns var INDEX() och MATCH() tillsammans standardmetoden för att kringgå VLOOKUP():s begränsningar. Det använder två funktioner i stället för en. 

• MATCH() hittar positionen för ditt uppslagsvärde. 

• INDEX() använder sedan den positionen för att hämta resultatet var du än pekar: vänster eller höger, det spelar ingen roll.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Jämför det med XLOOKUP()-versionen vi använt genom hela artikeln:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() fungerar bara i Microsoft 365 och Excel 2021 och framåt, som vi nämnde tidigare. Om din arbetsbok behöver öppnas i Excel 2019 eller 2016, eller om du delar med personer på äldre versioner, fungerar INDEX() och MATCH() fortfarande överallt.

Det finns också ett mindre fall. 

INDEX() och MATCH() separerar ”hitta positionen” från ”returnera värdet”, vilket hjälper i vissa komplexa nästlade formler. Detta dyker upp sällan, så de flesta läsare kommer inte att behöva det.

Utanför dessa två fall är XLOOKUP() det bättre valet för en ny arbetsbok. 

Vanliga XLOOKUP()-fel och hur du åtgärdar dem

Här är en snabb referens för de problem du troligast stöter på och hur du åtgärdar dem.

#N/A-fel

Detta är XLOOKUP():s standardsvar när den inte kan hitta en träff. Jag stötte på detta tidigare, där min lookup_value och lookup_array inte linjerade, och XLOOKUP() inte hade något att matcha mot i kolumn A.

Åtgärd: Dubbelkolla att din lookup_value faktiskt finns i din lookup_array, stavat och formaterat på samma sätt. Om saknade träffar är väntade och normala, använd argumentet if_not_found för att ersätta #N/A med något mer användbart, som "Not Found" eller en tom cell, som vi täckte tidigare. 

Felaktigt uppslagsområde

Detta händer när din lookup_array och return_array inte linjerar korrekt: fel rader, fel kolumner eller områden som inte matchar i storlek. 

Beroende på vilken version av Excel eller Sheets du använder kan detta visas som ett #VALUE!-fel, eller ibland som #N/A med ett separat meddelande som förklarar det verkliga problemet. Låt dig inte luras av #N/A att tro att det handlar om en saknad träff. 

Åtgärd: Se till att lookup_array och return_array täcker samma antal rader eller kolumner. Om lookup_array är A2:A5 måste return_array också spänna över raderna 2 till 5, inte raderna 4 till 5 eller något annat omatchat område.

Omatchade datatyper

VLOOKUP() kan ge fel eftersom den förväntade ett tal men fick text i stället. XLOOKUP() kan drabbas av ett liknande problem. Om din lookup_value är ett tal, men din lookup_array lagrar samma värden som text, eller tvärtom, hittar XLOOKUP() ingen träff, även om värdena ser identiska ut på skärmen.

Åtgärd: Kontrollera om din lookup_value och lookup_array använder samma datatyp. Tal som lagras som text visas oftast vänsterjusterade i en cell i stället för högerjusterade. Omslut din lookup_value i VALUE() för att konvertera text till tal, eller använd TEXT() för att göra tvärtom, beroende på riktningen på mismatchen.

Spillfel

Detta händer när ditt return_array täcker mer än en kolumn, men cellerna som XLOOKUP() behöver inte är tomma. Excel behöver ledigt utrymme bredvid din formelcell för att spilla resultaten. Om något redan finns där misslyckas hela formeln.

Åtgärd: Rensa cellerna där resultaten behöver hamna och skriv sedan in din formel. Om du behöver något i de cellerna, flytta din XLOOKUP()-formel till någon annanstans med fritt utrymme runt.

Avslutande tankar 

Du vet nu hur du använder XLOOKUP() för enstaka värden, flera kolumner, saknade data och till och med nästlade uppslag över två områden. Det är mycket mark täckt.

Gå vidare och gör XLOOKUP() till ditt standardval för varje ny arbetsbok. Det är mer flexibelt än VLOOKUP(), lättare att läsa än INDEX/MATCH(), och det sparar dig från att kapsla in varje formel i IFERROR().

Om du vill gå djupare är det också värt att lära sig INDEX() och MATCH() var för sig.