Excel 2020: Tolv fordeler med XLOOKUP - Excel-tips

Den nye XLOOKUP-funksjonen ruller ut til Office 365 fra november 2019. Joe McDaid fra Excel-teamet designet XLOOKUP for å forene menneskene som bruker VLOOKUP og menneskene som bruker INDEX / MATCH. Denne delen vil diskutere de 12 fordelene med XLOOKUP:

  1. Nøyaktig samsvar er standard.
  2. Heltalsbasert tredje argument av VLOOKUP er nå en skikkelig referanse.
  3. IFNA er innebygd for å håndtere manglende verdier.
  4. XLOOKUP har ikke noe problem å gå til venstre.
  5. Finn neste mindre eller neste større kamp uten å sortere tabellen.
  6. XLOOKUP kan gjøre HLOOKUP.
  7. Finn den siste kampen ved å søke fra bunnen.
  8. Jokertegn er som standard "av", men du kan slå dem på igjen.
  9. Returner alle 12 månedene i en enkelt formel.
  10. Kan returnere en cellehenvisning hvis XLOOKUP er ved siden av et kolon som XLOOKUP (); XLOOKUP ()
  11. Kan gjøre en toveiskamp som INDEX (, MATCH, MATCH) kan gjøre.
  12. Kan oppsummere alle oppslag i en enkelt formel som LOOKUP kan gjøre.

Her er syntaksen: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Fordel 1: Nøyaktig samsvar som standard

99% av VLOOKUP-formlene mine ender på, FALSE eller, 0 for å indikere en nøyaktig samsvar. Hvis du alltid bruker eksakt samsvarversjon av VLOOKUP, kan du begynne å la match_mode være utenfor XLOOKUP-funksjonen.

I den følgende figuren ser du opp W25-6 fra celle A4. Du vil se etter den varen i L8: L35. Når den er funnet, vil du ha den tilsvarende prisen fra kolonne N. Det er ikke nødvendig å spesifisere False som match_mode fordi XLOOKUP som standard er et eksakt samsvar.

XLOOKUP verdien i A4. Se i L8: L35. Returner tilsvarende pris fra N8: N35.

XLOOKUP Fordel 2: Results_Array er en referanse i stedet for et helt tall

Tenk på VLOOKUP-formelen du vil bruke før XLOOKUP. Det tredje argumentet ville ha vært en 3 for å indikere at du ønsket å returnere den tredje kolonnen. Det var alltid en fare for at en ideell kollega ville ha satt inn (eller slettet) en kolonne i tabellen din. Med en ekstra kolonne i tabellen, ville VLOOKUP som hadde returnert en pris begynne å returnere en beskrivelse. Fordi XLOOKUP pekte på en cellehenvisning, skriver formelen seg om for å fortsette å peke på prisen som nå er i kolonne O.

Den gamle VLOOKUP ville mislykkes hvis noen satte inn en ny kolonne i oppslagstabellen. XLOOKUP fortsetter å jobbe.

XLOOKUP Fordel 3: IFNA er innebygd som et valgfritt argument

Den fryktede # N / A-feilen returneres når oppslagsverdien ikke blir funnet i tabellen. Tidligere, for å erstatte # N / A med noe annet, må du bruke IFERROR eller IFNA pakket rundt VLOOKUP.

Når en vare ikke blir funnet, returnerer den # N / A fra VLOOKUP eller XLOOKUP …

Takket være et forslag fra Rico på YouTube-kanalen min, inkorporerte Excel-teamet et valgfritt fjerde argument for if_not_found. Hvis du vil erstatte disse # N / A-feilene med null, bare legg til, 0 som det fjerde argumentet. Eller du kan bruke litt tekst, for eksempel "Verdi ikke funnet".

Det valgfrie fjerde argumentet i XLOOKUP er "hvis ikke funnet". Sett en 0 eller "Ikke funnet" der.

XLOOKUP Fordel 4: Ikke noe problem å se til venstre for nøkkelfeltet

VLOOKUP kan ikke se til venstre for nøkkelfeltet uten å ty til VLOOKUP (A4, VALG ((1,2), G7: G34, F7: F34), 2, False). Med XLOOKUP er det ikke noe problem å ha Results_array til venstre for Lookup_array.

Med XLOOKUP er det ikke noe problem å returnere kategorien fra kolonne F mens du ser opp delenumre i kolonne G. Dette var alltid VLOOKUPs svakhet: den kunne ikke se til venstre.

XLOOKUP Fordel 5: Neste mindre eller neste større kamp uten sortering

VLOOKUP hadde et alternativ for å lete etter nøyaktig samsvar eller bare mindre verdi. Du kan enten la det fjerde argumentet være utenfor VLOOKUP, eller endre False til True. For at dette skulle fungere, måtte oppslagstabellen sorteres i stigende rekkefølge.

Et eksempel på Approximate Match-versjonen av VLOOKUP. Ethvert salg fra 10 tusen til 20 tusen får en bonus på 12 dollar.

Men VLOOKUP hadde ikke muligheten til å returnere nøyaktig samsvar eller neste større vare. For det måtte du bytte til å bruke MATCH med -1 som match_mode, og du måtte være forsiktig med at oppslagstabellen ble sortert synkende.

XLOOKUPs valgfrie femte argument match_mode kan bare se etter nøyaktig samsvar, lik eller bare mindre, lik eller bare større. Vær oppmerksom på at verdiene i XLOOKUP gir mer mening enn i MATCH:

  • -1 finner verdien lik eller bare mindre
  • 0 finn en nøyaktig samsvar
  • 1 finner verdien lik eller bare større.

Men, den mest fantastiske delen: oppslagstabellen trenger ikke å bli sortert, og noen match_mode vil fungere.

Nedenfor finner en match_mode på -1 det neste mindre elementet.

XLOOKUPs femte argument er Match_Mode. 0 er for eksakt samsvar. Negativ en brukes til eksakt samsvar eller neste mindre element. Positiv 1 er for eksakt samsvar eller neste større vare. 2 er for Wildcard Match. For å speile hva VLOOKUP med True i det fjerde argumentet ville gjøre, sett en negativ som match_mode-argumentet i XLOOKUP.

Her finner en match_mode på 1 hvilket kjøretøy som trengs, avhengig av antall personer i festen. Merk at oppslagstabellen ikke er sortert etter passasjerer, og kjøretøynavnet er til venstre for nøkkelen.

XLOOKUP kan gjøre noe VLOOKUP ikke kunne gjøre: finn nøyaktig samsvar eller bare større. I dette tilfellet har et reiseselskap en liste over reservasjoner. Basert på antall passasjerer viser oppslagstabellen hvilket kjøretøy du trenger for disse menneskene.

Tabellen sier:

  • Buss har plass til 64 personer
  • Bilen har plass til 4 personer
  • Motorcyle har plass til 1 person
  • Tour Van har 12 personer
  • Varebil holder 6 personer.

Som en bonus sorteres dataene etter kjøretøy (i den gamle løsningen, ved bruk av MATCH, må tabellen sorteres synkende etter kapasitet. Også: kjøretøyet er til venstre for kapasitet.

XLOOKUP Fordel 6: Sidelengs XLOOKUP erstatter HLOOKUP

Lookup_array og results_array kan være vannrett med XLOOKUP, noe som gjør det enkelt å erstatte HLOOKUP.

Her er oppslagstabellen horisontal. Tidligere vil dette kreve HLOOKUP, men XLOOKUP kan håndtere et bord som går sidelengs.

XLOOKUP Fordel 7: Søk fra bunnen av den siste kampen

Jeg har en gammel video på YouTube som svarer på et spørsmål fra en britisk hestegård. De hadde en bilpark. Hver gang et kjøretøy kom inn for drivstoff eller service, loggførte de kjøretøy, dato og kjørelengde i et regneark. De ønsket å finne den siste kjente kjørelengden for hvert kjøretøy. Mens MAXIFS i Excel-2017-epoken kanskje løste dette i dag, var løsningen for mange år siden en ukomplisert formel som brukte LOOKUP og involverte delingen med null.

I dag lar XLOOKUPs valgfrie sjette argument deg spesifisere at søket skal starte fra bunnen av datasettet.

Finn den siste kampen i listen.

Merk

Selv om dette er en stor forbedring, lar det deg bare finne den første eller siste kampen. Noen mennesker håpet at dette ville la deg finne den andre eller tredje kampen, men det er ikke meningen med argumentet search_mode.

Forsiktighet

Figuren over viser at det er søkemodus ved bruk av det gamle binære søket. Joe McDaid fraråder å bruke disse. For det første er den forbedrede oppslagsalgoritmen fra 2018 rask nok til at det ikke er noen betydelig hastighetsfordel. For det andre risikerer du at en ideell kollega sorterer oppslagstabellen og introduserer feil svar.

XLOOKUP Fordel 8: Jokertegn er "slått av" som standard

De fleste mennesker skjønte ikke at VLOOKUP behandler stjerne, spørsmålstegn og tilde som jokertegn som beskrevet i "# 51 Bruk et jokertegn i VLOOKUP" på side 143. Med XLOOKUP er jokertegn slått av som standard. Hvis du vil at XLOOKUP skal behandle disse tegnene som et jokertegn, bruker du 2 som Match_Mode.

Svært få mennesker skjønte at VLOOKUP behandler stjerner i oppslagsverdien som et jokertegn. Som standard bruker ikke XLOOKUP jokertegn, men du kan tvinge den til å oppføre seg som VLOOKUP hvis du bruker en Match Mode på 2: Jokertegn.

XLOOKUP Fordel 9: Returner alle 12 månedene i en enkelt formel!

Dette er virkelig en fordel med Dynamic Arrays, men det er min favorittgrunn til å elske XLOOKUP. Når du må returnere alle 12 månedene i et oppslag, vil en enkelt formel angitt i B6 med en rektangulær return_array returnere flere resultater. Disse resultatene vil strømme inn i tilstøtende celler.

I figuren nedenfor returnerer en enkelt formel i B7 alle 12 svarene vist i B7: M7.

En enkelt XLOOKUP i januar-kolonnen returnerer tall for januar til desember. Dette gjøres ved å spesifisere en resultatsammensetning med 12 kolonner.

XLOOKUP Fordel 10: Kan returnere en cellehenvisning hvis den ligger i nærheten av kolon

Denne er kompleks, men vakker. Tidligere var det syv funksjoner som ville endre seg fra å returnere en celleverdi til å returnere en cellehenvisning hvis funksjonen berørte et kolon. For eksempel, se Bruk A2: INDEKS () som en ikke-flyktig OFFSET. XLOOKUP er åttingsfunksjonen for å tilby denne oppførselen, og blir med i VELG, HVIS, IFS, INDEKS, INDIREKT, FORSKJELL og SVITSE.

Tenk på følgende figur. Noen velger Cherry i E4 og Fig i E5. Du vil ha en formel som vil oppsummere alt fra B6 til B9.

Figuren viser to XLOOKUP-formler i to celler. Den første returnerer 15 fra celle B6. Den andre kjører 30 fra B9. Men så i en tredje celle er det en formel som forbinder de to XLOOKUP-formlene med et kolon og deretter bryter den inn i en SUM-funksjon. Resultatet er SUM av B6: B9, fordi XLOOKUP kan returnere en cellereferanse hvis funksjonen vises ved siden av en operatør, for eksempel et kolon. For å bevise at dette fungerer, vil de neste tallene vise denne formelen i dialogboksen Evaluer formel.

I figuren ovenfor kan du se at en XLOOKUP av E4 vil returnere 15 fra celle B6. En XLOOKUP på E5 returnerer 30 fra B9. Men hvis du tar de to XLOOKUP-funksjonene fra cellene D9 og D10 og setter dem sammen med et kolon i mellom, endres oppførselen til XLOOKUP. I stedet for å returnere 15, returnerer den første XLOOKUP celleadressen B6!

For å bevise dette har jeg valgt D7 og bruker Formler, Evaluer formel. Etter å ha trykket på Evaluer to ganger, er neste del som skal beregnes XLOOKUP ("Cherry", A4: A29, B4: B29), som vist her.

Dette viser dialogboksen Evaluer formel like før du evaluerer den første XLOOKUP. Denne XLOOKUP vises rett før et kolon.

Trykk på Evaluer igjen og utrolig, XLOOKUP-formelen returnerer $ B $ 6 i stedet for de 15 som er lagret i B6. Dette skjer fordi det er et kolon umiddelbart etter denne XLOOKUP-formelen.

Klikk på Evaluer og den første XLOOKUP returnerer $ B $ 6 i stedet for 15.

Trykk på Evaluer to ganger til, og midlertidig formel vil være = SUM (B6: B9).

Etter å ha evaluert den andre XLOOKUP, er den midlertidige formelen = SUM (B6: B9).

Dette er fantastisk oppførsel som folk flest ikke vet om. Excel MVP Charles Williams forteller meg at det kan utløses med noen av disse tre operatørene ved siden av XLOOKUP:

  • Kolon
  • Space (kryssoperatør)
  • Komma (fagforening)

XLOOKUP Fordel 11: Toveiskamp som INDEX (, MATCH, MATCH)

For alle VLOOKUP-vennene mine har INDEX / MATCH-folk ventet på å se om XLOOKUP kan takle en toveiskamp. Den gode nyheten: den kan gjøre det. Den dårlige nyheten: Metoden er litt annerledes enn INDEX / MATCH-fansen forventer. Det kan være litt over hodet på dem. Men jeg er sikker på at de kan komme til denne metoden.

For en toveiskamp, ​​vil du finne hvilken rad som inneholder kontonummer A621 vist i J3. Så, XLOOKUP starter lett nok: = XLOOKUP (J3, A5: A15. Men så må du oppgi en results_array. Du kan bruke det samme trikset som i XLOOKUP Fordel 9: Returner alle 12 måneder i en enkelt formel ovenfor, men bruk den for å returnere en vertikal vektor. En indre XLOOKUP ser etter J4-måneden i månedens overskrifter i B4: G4. Return_array er spesifisert som B5: G15. Resultatet er at den indre XLOOKUP returnerer en matrise som den som er vist i I10 : I20 nedenfor. Siden A621 er funnet i den femte cellen i oppslagsarrayen og 104 er funnet i den femte cellen i resultatene, får du riktig svar fra formelen. Under viser J6 den gamle veien. J7 returnerer den nye veien.

XLookup J3 i listen over kontoer i A5: A15. For resultatoppstillingen, bruk XLOOKUP (J4, B4: G4, B5: G15). I denne formelen er B4: G4 en liste over måneder. B5: G15 er det rektangulære verdigrettet for alle kontoer i alle måneder. I en annen celle viser bare den indre XLOOKUP hvordan den returnerer hele verdikolonnen for mai.

XLOOKUP Fordel 12: Sum alle oppslagsverdier i en enkelt formel

Den gamle LOOKUP-funksjonen tilbød to rare triks. For det første, hvis du prøver å finne ut den totale mengden bonusutgifter som skal påløpe, kan du be LOOKUP om å slå opp alle verdiene i en enkelt formel. På bildet nedenfor ser LOOKUP (C4: C14 11 oppslag). Men LOOKUP-funksjonen ga ikke nøyaktig samsvar og krevde at oppslagstabellen skulle sorteres.

Slå opp 13 verdier og summer dem. Dette fungerte tidligere med LOOKUP, men det fungerer også med XLOOKUP. Spesifiser alle oppslagsverdiene C4: C14 som det første argumentet. Pakk inn XLOOKUP i en SUM-funksjon.

Med XLOOKUP kan du angi et område som lookup_value, og XLOOKUP vil returnere alle svarene. Fordelen er at XLOOKUP kan gjøre eksakte treff.

Trikset med å bruke LOOKUP for å oppsummere alle søkeresultatene, fungerte bare med den omtrentlige kampversjonen av Lookup. Her gjør XLOOKUP en nøyaktig samsvar på alle navnene i L4: L14 og får totalt alle resultatene.

Bonustips: Hva med en Twisted LOOKUP?

Excel MVP Mike Girvin viser ofte et triks for LOOKUP-funksjonen der Lookup_Vector er vertikal og Result_Vector er horisontal. XLOOKUP støtter ikke dette trikset. Men hvis du jukser litt og pakker results_array i TRANSPOSE-funksjonen, kan du administrere et vridd oppslag.

Her er oppslagsmatrisen vertikal og resultatmatrisen er horisontal. Den gamle LOOKUP-funksjonen kan takle dette, men for å gjøre det med XLOOKUP, må du pakke begge matriser i TRANSPOSE.

Interessante artikler...