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 matrixReturneringsværdi
Matchende værdi (r) fra returarraySyntaks
= 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 365Brugsanvisninger
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
- XLOOKUP kan arbejde med både lodrette og vandrette arrays.
- XLOOKUP returnerer # N / A, hvis opslagsværdien ikke findes.
- Den opslagsmatrixen skal have en dimension kompatibelt med return_array argument, ellers XLOOKUP vil vende tilbage #VALUE!
- Hvis XLOOKUP bruges mellem projektmapper, skal begge projektmapper være åbne, ellers returnerer XLOOKUP #REF !.
- Ligesom INDEX-funktionen returnerer XLOOKUP en reference som et resultat.