Hvordan bruke Excel XLOOKUP-funksjonen

Sammendrag

Excel XLOOKUP-funksjonen er en moderne og fleksibel erstatning for eldre funksjoner som VLOOKUP, HLOOKUP og LOOKUP. XLOOKUP støtter omtrentlig og nøyaktig samsvar, jokertegn (*?) For delvis samsvar og oppslag i vertikale eller horisontale områder.

Hensikt

Oppslagsverdier i rekkevidde eller matrise

Returverdi

Matchende verdi (r) fra returmatrise

Syntaks

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Argumenter

  • oppslag - Oppslagsverdien.
  • lookup_array - Matrisen eller området du vil søke.
  • return_array - Matrisen eller området som skal returneres.
  • not_found - (valgfritt) Verdi som skal returneres hvis ingen samsvar ble funnet.
  • match_mode - (valgfritt) 0 = eksakt samsvar (standard), -1 = eksakt samsvar eller neste minste, 1 = eksakt samsvar eller neste større, 2 = jokertegn.
  • søkemodus - (valgfritt) 1 = søk fra første (standard), -1 = søk fra siste, 2 = binært søk stigende, -2 = binært søk synkende.

Versjon

Excel 365

Bruksanvisninger

XLOOKUP er en moderne erstatning for VLOOKUP-funksjonen. Det er en fleksibel og allsidig funksjon som kan brukes i mange forskjellige situasjoner.

XLOOKUP kan finne verdier i vertikale eller horisontale områder, kan utføre omtrentlige og eksakte samsvar, og støtter jokertegn (*?) For delvis samsvar. I tillegg kan XLOOKUP søke i data fra den første eller den siste verdien (se samsvartype og søkemodusdetaljer nedenfor). Sammenlignet med eldre funksjoner som VLOOKUP, HLOOKUP og LOOKUP, tilbyr XLOOKUP flere viktige fordeler.

Ikke funnet melding

Når XLOOKUP ikke finner et samsvar, returnerer den feilen # N / A, som andre samsvaringsfunksjoner i Excel. I motsetning til de andre trekkfunksjonene, støtter XLOOKUP et valgfritt argument som heter not_found, som kan brukes til å legge feilen # N / A når den ellers ville vises. Typiske verdier for not_found kan være "Ikke funnet", "Ingen treff", "Ingen resultater" osv. Når du gir en verdi for not_found, legger du teksten i dobbel anførselstegn ("").

Merk: Vær forsiktig hvis du leverer en tom streng ("") for ikke_funnet. Hvis ingen treff blir funnet, vil XLOOKUP ikke vise noe i stedet for # N / A. Hvis du vil se # N / A-feilen når en kamp ikke blir funnet, kan du utelate argumentet helt.

Kamptype

Som standard vil XLOOKUP utføre en nøyaktig samsvar. Match atferd styres av et valgfritt argument kalt match_type, som har følgende alternativer:

Kamptype Oppførsel
0 (standard) Nøyaktig treff. Returnerer # N / A hvis det ikke samsvarer.
-1 Nøyaktig samsvar eller neste mindre element.
1 Nøyaktig samsvar eller neste større vare.
2 Jokertegn (*,?, ~)

Søkemodus

Som standard begynner XLOOKUP å matche fra den første dataverdien. Søkeoppførsel styres av et valgfritt argument kalt search_mode , som gir følgende alternativer:

Søkemodus Oppførsel
1 (standard) Søk fra første verdi
-1 Søk fra siste verdi (omvendt)
2 Binære søkeverdier sortert i stigende rekkefølge
-2 Binære søkeverdier sortert i synkende rekkefølge

Binære søk er veldig raske, men data må sorteres etter behov. Hvis data ikke er sortert riktig, kan et binært søk returnere ugyldige resultater som ser helt normale ut.

Eksempel 1 - grunnleggende eksakt samsvar

Som standard vil XLOOKUP utføre en nøyaktig samsvar. I eksemplet nedenfor brukes XLOOKUP til å hente salg basert på en nøyaktig samsvar på film. Formelen i H5 er:

=XLOOKUP(H4,B5:B9,E5:E9)

Mer detaljert forklaring her.

Eksempel 2 - grunnleggende omtrentlig kamp

For å aktivere en omtrentlig samsvar, oppgi en verdi for argumentet "match_mode". I eksemplet nedenfor brukes XLOOKUP til å beregne en rabatt basert på antall, som krever en omtrentlig kamp. Formelen i F5 leverer -1 for match_mode for å aktivere omtrentlig samsvar med "eksakt samsvar eller neste minste" oppførsel:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Mer detaljert forklaring her.

Eksempel # 3 - flere verdier

XLOOKUP kan returnere mer enn én verdi samtidig for samme kamp. Eksemplet nedenfor viser hvordan XLOOKUP kan konfigureres til å returnere tre samsvarende verdier med en enkelt formel. Formelen i C5 er:

=XLOOKUP(B5,B8:B15,C8:E15)

Legg merke til returmatrisen (C8: E15) inneholder tre kolonner: Første, Siste, Avdeling. Alle tre verdiene returneres og sprer seg inn i området C5: E5.

Eksempel 4 - toveisoppslag

XLOOKUP kan brukes til å utføre en toveisoppslag, ved å hekke en XLOOKUP i en annen. I eksemplet nedenfor henter den "indre" XLOOKUP en hel rad (alle verdier for Glass), som blir gitt til den "ytre" XLOOKUP som returmatrise. Den ytre XLOOKUP finner den aktuelle gruppen (B) og returnerer den tilsvarende verdien (17.25) som det endelige resultatet.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Flere detaljer her.

Eksempel nr. 5 - ikke funnet melding

Som andre oppslagsfunksjoner, hvis XLOOKUP ikke finner en verdi, returnerer den feilen # N / A. For å vise en egendefinert melding i stedet for # N / A, oppgi en verdi for det valgfrie argumentet "ikke funnet", omsluttet av doble anførselstegn (""). For eksempel, for å vise "Ikke funnet" når det ikke finnes noen samsvarende film, basert på regnearket nedenfor, bruk:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Du kan tilpasse denne meldingen som du vil: "Ingen treff", "Film ikke funnet" osv.

Eksempel 6 - komplekse kriterier

Med muligheten til å håndtere matriser naturlig, kan XLOOKUP brukes med komplekse kriterier. I eksemplet nedenfor samsvarer XLOOKUP med den første posten der: kontoen begynner med "x" og regionen er "øst" og måneden ikke er april:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Detaljer: (1) enkelt eksempel, (2) mer komplekst eksempel.

XLOOKUP fordeler

XLOOKUP tilbyr flere viktige fordeler, spesielt sammenlignet med VLOOKUP:

  • XLOOKUP kan slå opp data til høyre eller venstre for oppslagsverdier
  • XLOOKUP kan returnere flere resultater (eksempel # 3 ovenfor)
  • XLOOKUP er som standard et eksakt samsvar (VLOOKUP er omtrentlig)
  • XLOOKUP kan fungere med vertikale og horisontale data
  • XLOOKUP kan utføre et omvendt søk (siste til første)
  • XLOOKUP kan returnere hele rader eller kolonner, ikke bare én verdi
  • XLOOKUP kan arbeide med matriser for å anvende komplekse kriterier

Merknader

  1. XLOOKUP kan fungere med både vertikale og horisontale matriser.
  2. XLOOKUP returnerer # N / A hvis oppslagsverdien ikke blir funnet.
  3. Den søkematrise må ha en dimensjon kompatibel med return_array argument, ellers XLOOKUP vil returnere #VERDI!
  4. Hvis XLOOKUP brukes mellom arbeidsbøker, må begge arbeidsbøkene være åpne, ellers vil XLOOKUP returnere #REF !.
  5. I likhet med INDEX-funksjonen returnerer XLOOKUP en referanse som et resultat.

Relaterte videoer

Grunnleggende XLOOKUP-eksempel I denne videoen setter vi opp XLOOKUP-funksjonen med et grunnleggende eksempel. Hvis vi samsvarer med bynavn, henter vi land og befolkning. Grunnleggende XLOOKUP-omtrentlig kamp I denne videoen setter vi opp XLOOKUP-funksjonen til å utføre en omtrentlig kamp for å beregne en mengdebasert rabatt. XLOOKUP med boolsk logikk I denne videoen ser vi hvordan vi kan bruke XLOOKUP-funksjonen med boolsk logikk for å bruke flere kriterier. XLOOKUP med flere oppslagsverdier I denne videoen setter vi opp XLOOKUP for å returnere flere verdier i en dynamisk matrise ved å tilby et utvalg oppslagsverdier i stedet for en enkelt oppslagsverdi.

Interessante artikler...