Hur man använder XLOOKUP-funktionen i Excel 2016

Excel 2016 för Office 365-prenumeranter på Windows och Mac stöder nu en ny XLOOKUP-funktion, utpekad som en avsevärt enklare och mer mångsidig ersättning för den mycket populära (men ofta förtalade) vertikala lookup-funktionen VLOOKUP (vet inte vilket X i XLOOKUP står för; omfattande, kanske?).

För er som ännu inte är bekanta med VLOOKUP (som anses vara den tredje mest använda funktionen direkt efter SUM och AVERAGE), söker den här funktionen vertikalt efter rad i kolumnen längst till vänster i en angiven uppslagstabell uppifrån och ned tills den hittar ett värde i en uppslagskolumn betecknad med ett offsetnummer som matchar eller överstiger det du letar upp. Även om den är oerhört användbar för att lokalisera särskilda objekt i en lång lista eller kolumn i en datatabell i ditt kalkylblad, har VLOOKUP-funktionen flera begränsningar som inte delas av denna nya uppslagsfunktion, som XLOOKUP:

• Standard för att hitta exakta matchningar för ditt uppslagsvärde i uppslagsintervallet
• Kan söka både vertikalt (efter rad) och horisontellt (efter kolumn) i en tabell, vilket ersätter behovet av att använda HLOOKUP-funktionen vid sökning horisontellt efter kolumn
• Kan söka till vänster eller höger så att uppslagsintervallet i din uppslagstabell inte behöver vara placerat i en kolumn till vänster om den som anges som returintervall för att funktionen ska fungera
• När den exakta matchningsstandarden används fungerar även när värden i uppslagsintervallet inte sorteras i särskild ordning
• Kan söka från den nedre raden till toppen i uppslagsmatrisområdet med hjälp av ett valfritt söklägesargument

XLOOKUP-funktionen har fem möjliga argument, varav de tre första är obligatoriska och de två sista valfria, med hjälp av följande syntax:

XLOOKUP( lookup_value , lookup_array , return_array ,[ match_mode ],[ search_mode ])

Argumentet lookup_value som krävs anger värdet eller objektet som du söker efter. Argumentet look_up-matris som krävs anger cellintervallet som ska sökas efter detta uppslagsvärde, och return_array- argumentet anger cellintervallet som innehåller det värde som du vill ska returneras när Excel hittar en exakt matchning.

* Tänk på när du designerar argumenten lookup_array och return_array i din XLOOKUP-funktion, att båda intervallen måste vara lika långa, annars returnerar Excel #VALUE! fel i din formel. Detta är så mycket mer anledningen till att du använder intervallnamn eller kolumnnamn för en angiven datatabell när du definierar dessa argument snarare än att peka ut dem eller skriva in deras cellreferenser .

Det valfria argumentet match_mode kan innehålla något av följande fyra värden:

• 0 för en exakt matchning (standard, samma som när inget match_mode- argument anges)
• -1 för exakt matchning eller nästa lägre värde
• 1 för exakt matchning eller nästa högre värde
• 2 för partiell matchning med jokertecken kopplade till cellreferens i argumentet lookup_value

Det valfria argumentet search_mode kan innehålla något av följande fyra värden:

• 1 för att söka först till sist, det vill säga från topp till botten (standard, samma som när inget search_mode- argument anges)
• -1 för att söka sist-till-först, det vill säga botten till toppen
• 2 för en binär sökning i stigande ordning
• -2 för binär sökning i fallande ordning

Det bästa sättet att förstå kraften och mångsidigheten i den nya XLOOKUP-funktionen är att se den i funktion i ett Excel-kalkylblad. I följande figur har jag ett kalkylblad med en enkel 2019 försäljningsdatatabell ordnad efter land. För att använda XLOOKUP för att returnera den totala försäljningen från den här tabellen i cell E4 baserat på landet du anger i cell D4 i kalkylbladet, gör du dessa steg:

Placera cellmarkören i cell E4 i kalkylbladet

Klicka på alternativet Sök & referens på fliken Formler följt av XLOOKUP nära botten av rullgardinsmenyn för att öppna dialogrutan Funktionsargument.

Klicka på cell D4 i kalkylbladet för att ange dess cellreferens i textrutan Lookup_value-argument.

Tryck på Tabb för att välja argumentet Lookup_array, klicka sedan på cell A4 och håll ned Skift medan du trycker på Ctrl-nedåtpilen för att välja A4:A8 som intervall att söka (eftersom intervallet A3:B8 är definierat som en Excel-datatabell, Tabell1[Land] visas i textrutan i stället för intervallet A4:A8).

Tryck på Tabb för att välja argumentet Return_array, klicka sedan på cell B4 och håll ned Skift medan du trycker på Ctrl-nedåtpilen för att välja B4:B8 som intervallet som innehåller de värden som ska returneras baserat på resultaten av sökningen (som visas som Tabell1[Total försäljning] i textrutan).

Klicka på OK för att ange XLOOKUP-formeln i cell E4.

Skapa en formel med XLOOKUP i cell E4 som returnerar försäljning baserat på landet som anges i cell D4.

Excel matar in XLOOKUP-formeln i cell E4 i kalkylbladet och returnerar 4900 som resultat eftersom Costa Rica för närvarande är inlagd i uppslagscellen D4 och som du kan se i försäljningstabellen för 2019 är detta verkligen den totala försäljningen för detta land.

Eftersom XLOOKUP fungerar lika bra från höger till vänster som från vänster till höger, kan du använda denna funktion lika bra för att returnera landet från denna försäljningstabell baserat på en viss försäljningssiffra. Följande bild visar hur du gör detta. Den här gången skapar du formeln XLOOKUP i cell D4 och anger värdet som anges i cell E4 (11 000, i det här fallet) som argumentet lookup_value.

Dessutom anger du -1 som match_mode-argument för att åsidosätta funktionens exakta matchningsstandard så att Excel returnerar landet med en exakt matchning till försäljningsvärdet som anges i uppslagscellen E4 eller det med näst lägre total försäljning (Mexiko med 10 000 USD i det här fallet eftersom det inte finns något land i den här tabellen med 11 000 USD av den totala försäljningen). Utan att ange ett match_mode-argument för denna formel skulle Excel returnera #NA som resultat, eftersom det inte finns någon exakt matchning till $11 000 i den här försäljningstabellen.

Skapa en formel med XLOOKUP i cell D4 som returnerar landet baserat på försäljningen som anges i cell E4

Eftersom XLOOKUP-funktionen är lika bekväm att söka horisontellt efter kolumn som att söka vertikalt efter rad, kan du använda den för att skapa en formel som utför en tvåvägsuppslagning (ersätter behovet av att skapa en formel som kombinerar funktionerna INDEX och MATCH som förr). Följande figur, som innehåller produktionsschematabellen för 2019 för artikelnummer, AB-100 till AB-103 för månaderna april till december, visar hur detta går till.

Skapa en formel med kapslade XLOOKUP-funktioner för att returnera antalet producerade enheter för en del under en viss månad

I cell B12 skapade jag följande formel:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Den här formeln börjar med att definiera en XLOOKUP-funktion som vertikalt söker efter rad efter en exakt matchning med artikelinmatningen i cellen med namnet part_lookup (cell B10, i det här fallet) i cellintervallet $A$3:$A$6 i produktionstabellen . Observera dock att return_array-argumentet för den här ursprungliga SLOKUP-funktionen i sig är en andra XLOOKUP-funktion.

Denna andra, kapslade XLOOKUP-funktion söker i cellintervallet $B$2:$J$2 horisontellt efter kolumn för att hitta en exakt matchning med datuminmatningen i cellen som heter date_lookup (cell B11, i det här fallet). Argumentet return_array för denna andra, kapslade XLOOKUP-funktion är $B$3:$J$6, cellintervallet för alla produktionsvärden i tabellen.

Sättet som den här formeln fungerar är att Excel först beräknar resultatet av den andra, kapslade XLOOKUP-funktionen genom att utföra en horisontell sökning som, i det här fallet, returnerar matrisen i cellområdet D3: D6 i kolumnen Jun-19 (med värdena: 438, 153, 306 och 779) som dess resultat. Detta resultat blir i sin tur return_array-argumentet för den ursprungliga XLOOKUP-funktionen som utför en vertikal sökning efter rad för en exakt matchning med artikelnummerinmatningen i cell B11 (benämnt part_lookup). Eftersom den här part_lookup-cellen i det här exemplet innehåller AB-102, returnerar formeln bara produktionsvärdet Jun-19, 306, från resultatet av den andra, nästa XLOOKUP-funktionen.

Där har du det! En första titt på XLOOKUP, en kraftfull, mångsidig och ganska lättanvänd ny uppslagsfunktion som inte bara kan göra sökningar med enstaka värden som utförs av funktionerna VLOOKUP och HLOOKUP utan också de tvåvägsvärdessökningar som utförs genom att kombinera INDEX och MATCH fungerar också.

* Tyvärr är XLOOKUP-funktionen inte bakåtkompatibel med tidigare versioner av Microsoft Excel som endast stöder funktionerna VLOOKUP och HLOOKUP eller kompatibel med nuvarande versioner som ännu inte inkluderar den som en av deras uppslagsfunktioner, som Excel 2019 och Excel Online . Det betyder att om du delar en arbetsbok som innehåller XLOOKUP-formler med kollegor eller kunder som använder en version av Excel som inte innehåller den här nya uppslagsfunktionen, kommer alla dessa formler att returnera #NAMN? felvärden när de öppnar dess kalkylblad.