Sådan bruges Excel XLOOKUP-funktionen

Indholdsfortegnelse

Resumé

Excel XLOOKUP-funktionen er en moderne og fleksibel erstatning for ældre funktioner som VLOOKUP, HLOOKUP og LOOKUP. XLOOKUP understøtter omtrentlig og nøjagtig matchning, jokertegn (*?) Til delvise matches og opslag i lodrette eller vandrette områder.

Formål

Opslagsværdier i rækkevidde eller matrix

Returneringsværdi

Matchende værdi (r) fra returarray

Syntaks

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

Argumenter

  • opslag - Opslagsværdien.
  • lookup_array - Det array eller område, der skal søges.
  • return_array - Det array eller område, der skal returneres.
  • not_found - (valgfri) Værdi, der skal returneres, hvis der ikke findes nogen match.
  • match_mode - (valgfrit) 0 = nøjagtigt match (standard), -1 = nøjagtigt match eller næste mindste, 1 = nøjagtigt match eller næste større, 2 = jokertegn.
  • søgemåde - (valgfrit) 1 = søg fra første (standard), -1 = søg fra sidste, 2 = binær søgning stigende, -2 = binær søgning faldende.

Version

Excel 365

Brugsanvisninger

XLOOKUP er en moderne erstatning for VLOOKUP-funktionen. Det er en fleksibel og alsidig funktion, der kan bruges i en lang række situationer.

XLOOKUP kan finde værdier i lodrette eller vandrette områder, kan udføre omtrentlige og nøjagtige matches og understøtter jokertegn (*?) Til delvise matches. Derudover kan XLOOKUP søge i data startende fra den første eller den sidste værdi (se matchtype og søgetilstandsdetaljer nedenfor). Sammenlignet med ældre funktioner som VLOOKUP, HLOOKUP og LOOKUP tilbyder XLOOKUP flere vigtige fordele.

Ikke fundet meddelelse

Når XLOOKUP ikke kan finde et match, returnerer det fejlen # N / A, ligesom andre matchfunktioner i Excel. I modsætning til de andre matchfunktioner understøtter XLOOKUP et valgfrit argument kaldet not_found, der kan bruges til at overse # N / A-fejlen, når den ellers ville vises. Typiske værdier for not_found kan være "Ikke fundet", "Intet match", "Intet resultat" osv. Når du angiver en værdi for not_found, skal du omslutte teksten med dobbelt anførselstegn ("").

Bemærk: Vær forsigtig, hvis du angiver en tom streng ("") for ikke_fundet. Hvis der ikke findes noget match, viser XLOOKUP intet i stedet for # N / A. Hvis du vil se fejlen # N / A, når et match ikke findes, skal du udelade argumentet helt.

Kamptype

XLOOKUP udfører som standard et nøjagtigt match. Matchadfærd styres af et valgfrit argument kaldet match_type, som har følgende muligheder:

Kamptype Opførsel
0 (standard) Præcis match. Returnerer # N / A, hvis der ikke er nogen match.
-1 Præcis match eller næste mindre element.
1 Præcis match eller næste større vare.
2 Jokertegnkamp (*,?, ~)

Søgningstilstand

XLOOKUP begynder som standard at matche fra den første dataværdi. Søgeadfærd styres af et valgfrit argument kaldet search_mode , som giver følgende muligheder:

Søgningstilstand Opførsel
1 (standard) Søg fra første værdi
-1 Søg fra sidste værdi (omvendt)
2 Binære søgningsværdier sorteret i stigende rækkefølge
-2 Binære søgningsværdier sorteret i faldende rækkefølge

Binære søgninger er meget hurtige, men data skal sorteres efter behov. Hvis data ikke sorteres korrekt, kan en binær søgning returnere ugyldige resultater, der ser helt normale ud.

Eksempel # 1 - grundlæggende nøjagtig matchning

XLOOKUP udfører som standard et nøjagtigt match. I eksemplet nedenfor bruges XLOOKUP til at hente salg baseret på et nøjagtigt match på film. Formlen i H5 er:

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

Mere detaljeret forklaring her.

Eksempel # 2 - grundlæggende omtrentlig kamp

For at aktivere et omtrentligt match skal du angive en værdi for argumentet "match_mode". I eksemplet nedenfor bruges XLOOKUP til at beregne en rabat baseret på mængde, hvilket kræver en omtrentlig match. Formlen i F5 leverer -1 til match_mode for at aktivere omtrentlig matchning med "nøjagtig match eller næste mindste" opførsel:

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

Mere detaljeret forklaring her.

Eksempel # 3 - flere værdier

XLOOKUP kan returnere mere end en værdi på samme tid til den samme kamp. Eksemplet nedenfor viser, hvordan XLOOKUP kan konfigureres til at returnere tre matchende værdier med en enkelt formel. Formlen i C5 er:

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

Bemærk retur array (C8: E15) inkluderer 3 kolonner: Første, Sidste, Afdeling. Alle tre værdier returneres og spilder ind i området C5: E5.

Eksempel # 4 - tovejs opslag

XLOOKUP kan bruges til at udføre en tovejs opslag ved at indlejre en XLOOKUP inde i en anden. I eksemplet nedenfor henter den "indre" XLOOKUP en hel række (alle værdier for Glass), som afleveres til den "ydre" XLOOKUP som returarray. Den ydre XLOOKUP finder den relevante gruppe (B) og returnerer den tilsvarende værdi (17.25) som det endelige resultat.

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

Flere detaljer her.

Eksempel # 5 - ikke fundet meddelelse

Ligesom andre opslagsfunktioner, hvis XLOOKUP ikke finder en værdi, returnerer den fejlen # N / A. For at få vist en brugerdefineret meddelelse i stedet for # N / A, skal du angive en værdi for det valgfri argument "ikke fundet", omgivet af dobbelt anførselstegn (""). For eksempel for at vise "Ikke fundet", når der ikke findes nogen matchende film, baseret på regnearket nedenfor, skal du bruge:

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

Du kan tilpasse denne meddelelse, som du vil: "Ingen match", "Film ikke fundet" osv.

Eksempel # 6 - komplekse kriterier

Med evnen til at håndtere arrays indbygget kan XLOOKUP bruges med komplekse kriterier. I eksemplet nedenfor matcher XLOOKUP den første post, hvor: konto begynder med "x" og region er "øst" og måned ikke er april:

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

Detaljer: (1) simpelt eksempel, (2) mere komplekst eksempel.

XLOOKUP fordele

XLOOKUP tilbyder flere vigtige fordele, især sammenlignet med VLOOKUP:

  • XLOOKUP kan slå data til højre eller venstre for opslagsværdier
  • XLOOKUP kan returnere flere resultater (eksempel # 3 ovenfor)
  • XLOOKUP er som standard et nøjagtigt match (VLOOKUP er som standard)
  • XLOOKUP kan arbejde med lodrette og vandrette data
  • XLOOKUP kan udføre en omvendt søgning (sidste til første)
  • XLOOKUP kan returnere hele rækker eller kolonner, ikke kun en værdi
  • XLOOKUP kan arbejde med arrays indbygget for at anvende komplekse kriterier

Bemærkninger

  1. XLOOKUP kan arbejde med både lodrette og vandrette arrays.
  2. XLOOKUP returnerer # N / A, hvis opslagsværdien ikke findes.
  3. Den opslagsmatrixen skal have en dimension kompatibelt med return_array argument, ellers XLOOKUP vil vende tilbage #VALUE!
  4. Hvis XLOOKUP bruges mellem projektmapper, skal begge projektmapper være åbne, ellers returnerer XLOOKUP #REF !.
  5. Ligesom INDEX-funktionen returnerer XLOOKUP en reference som et resultat.

Lignende videoer

Grundlæggende XLOOKUP-eksempel I denne video opretter vi XLOOKUP-funktionen med et grundlæggende eksempel. Hvis vi matcher bynavnet, henter vi land og befolkning. Grundlæggende XLOOKUP omtrentlig kamp I denne video opretter vi XLOOKUP-funktionen til at udføre en omtrentlig kamp for at beregne en mængdebaseret rabat. XLOOKUP med boolsk logik I denne video ser vi på, hvordan man bruger XLOOKUP-funktionen med boolsk logik til at anvende flere kriterier. XLOOKUP med flere opslagsværdier I denne video opretter vi XLOOKUP til at returnere flere værdier i et dynamisk array ved at give en række opslagsværdier i stedet for en enkelt opslagsværdi.

Interessante artikler...