Excel 2020: Tolv fördelar med XLOOKUP - Excel-tips

Den nya XLOOKUP-funktionen rullar ut till Office 365 från och med november 2019. Joe McDaid från Excel-teamet utformade XLOOKUP för att förena de människor som använder VLOOKUP och de personer som använder INDEX / MATCH. Detta avsnitt kommer att diskutera de 12 fördelarna med XLOOKUP:

  1. Exakt matchning är standard.
  2. Heltalsbaserat tredje argument för VLOOKUP är nu en riktig referens.
  3. IFNA är inbyggt för att hantera saknade värden.
  4. XLOOKUP har inga problem att gå till vänster.
  5. Hitta nästa mindre eller nästa större match utan att sortera tabellen.
  6. XLOOKUP kan göra HLOOKUP.
  7. Hitta den senaste matchen genom att söka från botten.
  8. Jokertecken är "av" som standard, men du kan aktivera dem igen.
  9. Returnera alla 12 månader i en enda formel.
  10. Kan returnera en cellreferens om XLOOKUP är bredvid ett kolon som XLOOKUP (); XLOOKUP ()
  11. Kan göra en tvåvägsmatchning som INDEX (, MATCH, MATCH) kan göra.
  12. Kan summera alla uppslag i en enda formel som LOOKUP kan göra.

Här är syntaxen: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Fördel 1: Exakt matchning som standard

99% av mina VLOOKUP-formler slutar på, FALSE eller, 0 för att ange en exakt matchning. Om du alltid använder den exakta matchningsversionen av VLOOKUP kan du börja lämna match_mode från din XLOOKUP-funktion.

I följande bild letar du upp W25-6 från cell A4. Du vill leta efter det föremålet i L8: L35. När den hittas vill du ha motsvarande pris från kolumn N. Det finns inget behov av att ange False som matchningsläge eftersom XLOOKUP är som standard en exakt matchning.

XLOOKUP värdet i A4. Titta i L8: L35. Returnera motsvarande pris från N8: N35.

XLOOKUP Fördel 2: Results_Array är en referens istället för ett heltal

Tänk på VLOOKUP-formeln som du skulle använda före XLOOKUP. Det tredje argumentet skulle ha varit en 3 för att indikera att du ville returnera den tredje kolumnen. Det fanns alltid en risk att en ledig medarbetare skulle ha infogat (eller raderat) en kolumn i din tabell. Med en extra kolumn i tabellen skulle VLOOKUP som hade returnerat ett pris börja returnera en beskrivning. Eftersom XLOOKUP pekade på en cellreferens skrivs formeln om för att fortsätta peka på det pris som nu finns i kolumn O.

Den gamla VLOOKUP skulle misslyckas om någon infogade en ny kolumn i uppslagstabellen. XLOOKUP fortsätter att fungera.

XLOOKUP Fördel 3: IFNA är inbyggt som ett valfritt argument

Det fruktade # N / A-felet returneras när ditt sökvärde inte finns i tabellen. Tidigare, för att ersätta # N / A med något annat, måste du använda IFERROR eller IFNA insvept runt VLOOKUP.

När ett objekt inte hittas returnerar det # N / A från VLOOKUP eller XLOOKUP …

Tack vare ett förslag från Rico på min YouTube-kanal införde Excel-teamet ett valfritt fjärde argument för if_not_found. Om du vill ersätta dessa # N / A-fel med noll, lägg bara till, 0 som det fjärde argumentet. Eller så kan du använda lite text, till exempel "Värde hittades inte".

Det valfria fjärde argumentet i XLOOKUP är "om det inte hittas". Sätt ett 0 eller "Hittades inte" där.

XLOOKUP Fördel 4: Inga problem att titta till vänster om nyckelfältet

VLOOKUP kan inte titta till vänster om nyckelfältet utan att använda VLOOKUP (A4, VÄLJ ((1,2), G7: G34, F7: F34), 2, Falskt). Med XLOOKUP är det inga problem att ha Results_array till vänster om Lookup_array.

Med XLOOKUP finns det inga problem att returnera kategorin från kolumn F medan du letar upp artikelnummer i kolumn G. Detta var alltid VLOOKUPs svaghet: det kunde inte se till vänster.

XLOOKUP Fördel 5: Nästa mindre eller nästa större match utan sortering

VLOOKUP hade möjlighet att leta efter exakt matchning eller bara mindre värde. Du kan antingen lämna det fjärde argumentet ur VLOOKUP eller ändra False till True. För att detta ska fungera måste uppslagstabellen sorteras i stigande ordning.

Ett exempel på den ungefärliga versionen av VLOOKUP. Varje försäljning från 10 tusen till 20 tusen får en bonus på 12 dollar.

Men VLOOKUP hade inte förmågan att returnera exakt matchning eller nästa större artikel. För det var du tvungen att byta till att använda MATCH med -1 som match_mode och du var tvungen att vara försiktig med att uppslagstabellen sorterades fallande.

XLOOKUP: s valfria femte argument match_mode kan bara leta efter exakt matchning, lika med eller bara mindre, lika med eller bara större. Observera att värdena i XLOOKUP är mer meningsfulla än i MATCH:

  • -1 hittar värdet lika med eller bara mindre
  • 0 hitta en exakt matchning
  • 1 hittar värdet lika med eller bara större.

Men den mest fantastiska delen: uppslagstabellen behöver inte sorteras och någon match_mode fungerar.

Nedan hittar en matchningsläge på -1 nästa mindre objekt.

XLOOKUP: s femte argument är Match_Mode. 0 är för exakt matchning. Negativ en används för exakt matchning eller nästa mindre artikel. Positiv 1 är för exakt matchning eller nästa större artikel. 2 är för Wildcard Match. För att spegla vad VLOOKUP med True i det fjärde argumentet skulle göra, sätt ett negativt som match_mode-argumentet i XLOOKUP.

Här, en matchningsläge på 1, hittar du vilket fordon som behövs beroende på antalet personer i partiet. Observera att uppslagstabellen inte sorteras efter passagerare och fordonets namn finns till vänster om nyckeln.

XLOOKUP kan göra något VLOOKUP kunde inte göra: hitta exakt matchning eller bara större. I det här fallet har ett reseföretag en lista med reservationer. Baserat på antalet passagerare visar uppslagstabellen vilket fordon du behöver för dessa människor.

Tabellen säger:

  • Buss rymmer 64 personer
  • Bilen rymmer 4 personer
  • Motorcykel rymmer 1 person
  • Tour Van rymmer 12 personer
  • Van rymmer 6 personer.

Som en bonus sorteras data efter fordon (i den gamla lösningen, med hjälp av MATCH, måste tabellen sorteras nedåt efter kapacitet. Dessutom: Fordonet är till vänster om kapacitet.

XLOOKUP Fördel 6: Sidled XLOOKUP ersätter HLOOKUP

Lookup_array och results_array kan vara horisontella med XLOOKUP, vilket gör det enkelt att byta ut HLOOKUP.

Här är uppslagstabellen horisontell. Tidigare skulle detta kräva HLOOKUP, men XLOOKUP kan hantera ett bord som går i sidled.

XLOOKUP Fördel 7: Sök från botten för den senaste matchen

Jag har en gammal video på YouTube som svarar på en fråga från en brittisk hästgård. De hade en fordonsflotta. Varje gång ett fordon kom in för bränsle eller service loggade de in fordon, datum och körsträcka i ett kalkylblad. De ville hitta den senaste kända körsträckan för varje fordon. Medan Excel-2017-eran MAXIFS kan lösa detta idag, var lösningen för många år sedan en arkanformel med LOOKUP och involverad delning med noll.

Idag låter XLOOKUPs valfria sjätte argument dig ange att sökningen ska börja från undersidan.

Hitta den senaste matchen i listan.

Notera

Även om detta är en stor förbättring kan du bara hitta den första eller sista matchen. Vissa människor hoppades att detta skulle låta dig hitta den andra eller tredje matchningen, men det är inte avsikten med argumentet search_mode.

Varning

Bilden ovan visar att det finns söklägen med den gamla binära sökningen. Joe McDaid avråder från att använda dessa. För det första är den förbättrade sökalgoritmen från 2018 tillräckligt snabb för att det inte finns någon betydande hastighetsfördel. För det andra riskerar du att en ledig medarbetare sorterar uppslagstabellen och inför fel svar.

XLOOKUP Fördel 8: Jokertecken är "avstängda" som standard

De flesta insåg inte att VLOOKUP behandlar asterisk, frågetecken och tilde som jokertecken som beskrivs i "# 51 Använd ett jokertecken i VLOOKUP" på sidan 143. Med XLOOKUP är jokertecken avstängda som standard. Om du vill att XLOOKUP ska behandla dessa tecken som ett jokertecken, använd 2 som Match_Mode.

Väldigt få människor insåg att VLOOKUP behandlar asterisker i uppslagsvärdet som ett jokertecken. Som standard använder XLOOKUP inte jokertecken, men du kan tvinga det att bete sig som VLOOKUP om du använder ett matchningsläge 2: jokerteckenmatchning.

XLOOKUP Fördel 9: Returnera alla 12 månader i en enda formel!

Detta är verkligen en fördel med Dynamic Arrays, men det är min favoritskäl att älska XLOOKUP. När du måste returnera alla 12 månaderna i en uppslagning kommer en enda formel som anges i B6 med en rektangulär return_array att returnera flera resultat. Dessa resultat kommer att spilla i intilliggande celler.

I figuren nedan returnerar en enda formel som anges i B7 alla 12 svar som visas i B7: M7.

En enda XLOOKUP i januari-kolumnen returnerar siffror för januari till december. Detta görs genom att ange ett resultatarray med 12 kolumner.

XLOOKUP Fördel 10: Kan returnera en cellreferens om den ligger intill kolon

Den här är komplex men vacker. Tidigare fanns det sju funktioner som skulle ändras från att returnera ett cellvärde till att returnera en cellreferens om funktionen rörde ett kolon. För ett exempel, se Använd A2: INDEX () som en icke-flyktig OFFSET. XLOOKUP är åttandefunktionen för att erbjuda detta beteende genom att gå med i VÄLJ, OM, IFS, INDEX, INDIRECT, OFFSET och SWITCH.

Tänk på följande bild. Någon väljer Cherry i E4 och Fig i E5. Du vill ha en formel som sammanfattar allt från B6 till B9.

Figuren visar två XLOOKUP-formler i två celler. Den första returnerar 15 från cell B6. Den andra återkallar 30 från B9. Men i en tredje cell finns det en formel som sammanfogar de två XLOOKUP-formlerna med ett kolon och sedan slår in den i en SUM-funktion. Resultatet är SUMMET för B6: B9, eftersom XLOOKUP kan returnera en cellreferens om funktionen visas bredvid en operatör som ett kolon. För att bevisa att detta fungerar kommer de följande siffrorna att visa denna formel i dialogrutan Utvärdera formel.

I figuren ovan kan du se att en XLOOKUP av E4 kommer att returnera 15 från cell B6. En XLOOKUP på E5 returnerar 30 från B9. Men om du tar de två XLOOKUP-funktionerna från cellerna D9 och D10 och sätter ihop dem med ett kolon däremellan, ändras beteendet hos XLOOKUP. Istället för att returnera 15 returnerar den första XLOOKUP celladressen B6!

För att bevisa detta har jag valt D7 och använder formler, utvärdera formel. Efter att ha tryckt på Utvärdera två gånger är nästa del som ska beräknas XLOOKUP ("Cherry", A4: A29, B4: B29), som visas här.

Detta visar dialogrutan Utvärdera formel precis innan den första XLOOKUP utvärderas. Denna XLOOKUP visas precis före ett kolon.

Tryck på Utvärdera igen och förvånansvärt, XLOOKUP-formeln returnerar $ B $ 6 istället för de 15 lagrade i B6. Detta händer eftersom det finns ett kolon direkt efter denna XLOOKUP-formel.

Klicka på Utvärdera och den första XLOOKUP returnerar $ B $ 6 istället för 15.

Tryck på Utvärdera två gånger till, och interimsformeln blir = SUMMA (B6: B9).

Efter utvärdering av den andra XLOOKUP är interimsformeln = SUM (B6: B9).

Det här är fantastiskt beteende som de flesta inte känner till. Excel MVP Charles Williams berättar för mig att det kan utlösas med någon av dessa tre operatörer bredvid XLOOKUP:

  • Kolon
  • Space (korsningsoperatör)
  • Komma (facklig operatör)

XLOOKUP Fördel 11: Tvåvägsmatchning som INDEX (, MATCH, MATCH)

För alla mina VLOOKUP-vänner har INDEX / MATCH-personerna väntat på om XLOOKUP klarar en tvåvägsmatch. De stora nyheterna: det kan göra det. De dåliga nyheterna: metoden är lite annorlunda än vad INDEX / MATCH-fansen förväntar sig. Det kan vara lite över huvudet på dem. Men jag är säker på att de kan komma till den här metoden.

För en tvåvägsmatchning vill du hitta vilken rad som innehåller kontonumret A621 som visas i J3. Så, XLOOKUP börjar tillräckligt enkelt: = XLOOKUP (J3, A5: A15. Men då måste du ange ett resultat_array. Du kan använda samma trick som i XLOOKUP Fördel 9: Returnera alla 12 månader i en enda formel ovan, men använd den för att returnera en vertikal vektor. En inre XLOOKUP letar efter J4-månaden i månadens rubriker i B4: G4. Return_array anges som B5: G15. Resultatet är att den inre XLOOKUP returnerar en array som den som visas i I10 : I20 nedan. Eftersom A621 finns i den femte cellen i lookup_array och 104 finns i den femte cellen i results_array, får du rätt svar från formeln. Nedan visar J6 det gamla sättet. J7 returnerar det nya sättet.

XLookup J3 i kontolistan i A5: A15. För resultatmatningen, använd XLOOKUP (J4, B4: G4, B5: G15). I denna formel är B4: G4 en lista över månader. B5: G15 är den rektangulära värden för alla konton för alla månader. I en annan cell visar bara den inre XLOOKUP hur den returnerar hela kolumnen med värden för maj.

XLOOKUP Fördel 12: Sammanfatta alla sökvärden i en enda formel

Den gamla LOOKUP-funktionen erbjöd två konstiga knep. För det första, om du försöker räkna ut det totala beloppet för bonusutgifter som ska tillkomma, kan du be LOOKUP att slå upp alla värden i en enda formel. I bilden nedan gör LOOKUP (C4: C14 11 sökningar. Men LOOKUP-funktionen gav inte en exakt matchning och krävde att uppslagstabellen skulle sorteras.

Sök 13 värden och summera dem. Detta fungerade tidigare med LOOKUP, men det fungerar också med XLOOKUP. Ange alla uppslagsvärden C4: C14 som det första argumentet. Slå in XLOOKUP i en SUM-funktion.

Med XLOOKUP kan du ange ett intervall som uppslagsvärde och XLOOKUP returnerar alla svaren. Fördelen är att XLOOKUP kan göra exakta matchningar.

Tricket att använda LOOKUP för att summera alla sökresultaten fungerade bara med den ungefärliga matchningsversionen av Lookup. Här gör XLOOKUP en exakt matchning på alla namn i L4: L14 och får totalt alla resultat.

Bonus Tips: Vad sägs om en Twisted LOOKUP?

Excel MVP Mike Girvin visar ofta ett trick för LOOKUP-funktionen där Lookup_Vector är vertikal och Result_Vector är horisontell. XLOOKUP stöder inte detta trick. Men om du fuskar lite och slår in results_array i TRANSPOSE-funktionen kan du hantera en snodd uppslagning.

Här är uppslagsmatrisen vertikal och resultatmatrisen horisontell. Den gamla LOOKUP-funktionen klarar detta, men för att göra det med XLOOKUP måste du slå in endera arrayen i TRANSPOSE.

Intressanta artiklar...