Excel XLOOKUP -funktion: Alt hvad du behøver at vide (10 eksempler)

Se video - Excel XLOOKUP -funktion (10 XLOOKUP -eksempler)

Excel XLOOKUP -funktion er endelig kommet.

Hvis du har brugt VLOOKUP eller INDEX/MATCH, er jeg sikker på, at du vil elske den fleksibilitet, XLOOKUP -funktionen giver.

I denne vejledning dækker jeg alt, hvad der er at vide om XLOOKUP -funktionen og nogle eksempler, der hjælper dig med at vide, hvordan du bedst bruger den.

Så lad os komme i gang!

Hvad er XLOOKUP?

XLOOKUP er en ny funktion er Office 365 og er en ny og forbedret version af VLOOKUP/HLOOKUP -funktionen.

Det gør alt, hvad VLOOKUP plejede at gøre, og meget mere.

XLOOKUP er en funktion, der giver dig mulighed for hurtigt at lede efter en værdi i et datasæt (lodret eller vandret) og returnere den tilsvarende værdi i en anden række/kolonne.

For eksempel, hvis du har fået karaktererne for elever i en eksamen, kan du bruge XLOOKUP til hurtigt at kontrollere, hvor meget en elev har scoret ved hjælp af elevens navn.

Kraften i denne funktion bliver endnu mere tydelig, når jeg dyber dybt ned i nogle XLOOKUP eksempler senere i denne vejledning.

Men inden jeg kommer ind på eksemplerne, er der et stort spørgsmål - hvordan får jeg adgang til XLOOKUP?

Sådan får du adgang til XLOOKUP?

Fra nu af er XLOOKUP kun tilgængelig for brugerne af Office 365.

Så hvis du bruger tidligere versioner af Excel (2010/2013/2016/2019), kan du ikke bruge denne funktion.

Jeg er heller ikke sikker på, om dette nogensinde ville blive frigivet til tidligere versioner eller ej (måske kan Microsoft oprette et tilføjelsesprogram, som de gjorde for Power Query). Men fra nu af får du kun brugt det, hvis du er på Office 365.

Klik her for at opgradere til Office 365

Hvis du allerede er på Office 365 (Home, Personal eller University edition) og ikke har adgang til det, kan du gå til fanen Filer og derefter klikke på Konto.

Der ville være et Office Insider -program, og du kan klikke og deltage i Office Insider -programmet. Dette giver dig adgang til XLOOKUP -funktionen.

Jeg forventer, at XLOOKUP snart er tilgængelig på alle Office 365 -versioner.

Bemærk: XLOOKUP er også tilgængelig til Office 365 til Mac og Excel til internettet (Excel online)

XLOOKUP Funktionssyntaks

Nedenfor er syntaksen for XLOOKUP -funktionen:

= XLOOKUP (opslag_værdi, opslag_array, retur_array, [hvis_not_fundet], [match_mode], [search_mode])

Hvis du har brugt VLOOKUP, vil du bemærke, at syntaksen er ret ens, med nogle fantastiske ekstra funktioner naturligvis.

Bare rolig, hvis syntaksen og argumentet ser lidt for meget ud. Jeg dækker disse med nogle lette XLOOKUP -eksempler senere i denne vejledning, der gør det krystalklart.

XLOOKUP -funktionen kan fortælle 6 argumenter (3 obligatoriske og 3 valgfrie):

1. opslagsværdi - den værdi, du leder efter
2. opslag_array - arrayet, hvor du leder efter opslagsværdien
3. return_array - den matrix, du vil hente og returnere værdien fra (svarende til den position, hvor opslagsværdien findes)
4. [if_not_found] - værdien, der skal returneres, hvis opslagsværdien ikke findes. Hvis du ikke angiver dette argument, returneres en #N/A fejl
5. [match_mode] - Her kan du angive den type match, du ønsker:
   • 0 - Præcis match, hvor opslagsværdien nøjagtigt skal matche værdien i opslag_arrayet. Dette er standardindstillingen.
   • -1 - Leder efter det nøjagtige match, men hvis det er fundet, returnerer det næste mindre element/værdi
   • 1 - Leder efter det nøjagtige match, men hvis det er fundet, returnerer det næste større element/værdi
   • 2 - For at foretage delvis matchning ved hjælp af jokertegn (* eller ~)
6. [search_mode] - Her angiver du, hvordan XLOOKUP -funktionen skal søge i lookup_array
   • 1 - Dette er standardindstillingen, hvor funktionen begynder at lede efter opslagsværdien fra toppen (første element) til bunden (sidste element) i opslag_arrayet
   • -1 - Gør søgningen fra bund til top. Nyttigt, når du vil finde den sidste matchende værdi i opslag_arrayet
   • 2 - Udfører en binær søgning, hvor dataene skal sorteres i stigende rækkefølge. Hvis det ikke sorteres, kan dette give fejl eller forkerte resultater
   • -2 - Udfører en binær søgning, hvor dataene skal sorteres i faldende rækkefølge. Hvis det ikke sorteres, kan dette give fejl eller forkerte resultater

XLOOKUP Funktion Eksempler

Lad os nu komme til den interessante del - nogle praktiske XLOOKUP -eksempler.

Disse eksempler hjælper dig med bedre at forstå, hvordan XLOOKUP fungerer, hvordan det er forskelligt fra VLOOKUP og INDEX/MATCH og nogle forbedringer og begrænsninger af denne funktion.

Klik her for at downloade eksempelfilen og følge med

Eksempel 1: Hent en opslagsværdi

Antag, at du har følgende datasæt, og du vil hente matematikscoren for Greg (opslagsværdien).

Nedenfor er formlen, der gør dette:

= XLOOKUP (F2, A2: A15, B2: B15)

I ovenstående formel har jeg lige brugt de obligatoriske argumenter, hvor det leder efter navnet (fra top til bund), finder et nøjagtigt match og returnerer den tilsvarende værdi fra B2: B15.

En tydelig forskel, XLOOKUP og VLOOKUP -funktionen har, er den måde, de håndterer opslag array. I VLOOKUP har du hele arrayet, hvor opslagsværdien er i kolonnen længst til venstre, og derefter angiver du kolonnummeret, hvorfra du vil hente resultatet. XLOOKUP giver dig på den anden side mulighed for at vælge lookup_array og return_array separat

En øjeblikkelig fordel ved at have lookup_array og return_array som separate argumenter betyder, at nu kan du se til venstre. VLOOKUP havde denne begrænsning, hvor du kun kan slå op og finde en værdi, der er til højre. Men med XLOOKUP er denne begrænsning væk.

Her er et eksempel. Jeg har det samme datasæt, hvor navnet er til højre og return_range er til venstre.

Nedenfor er formlen, som jeg kan bruge til at få scoren for Greg i matematik (hvilket betyder at se til venstre for opslagsværdien)

= XLOOKUP (F2, D2: D15, A2: A15)

XLOOKUP løser et andet stort problem - Hvis du indsætter en ny kolonne eller flytter kolonner rundt, vil de resulterende data stadig være korrekte. VLOOKUP ville sandsynligvis gå i stykker eller give et forkert resultat i sådanne tilfælde, da kolonneindeksværdien oftest er hårdkodet.

Eksempel 2: Slå op og hent en hel post

Lad os tage de samme data som et eksempel.

I dette tilfælde vil jeg ikke bare hente Gregs score i matematik. Jeg vil have scoringer i alle fagene.

I dette tilfælde kan jeg bruge nedenstående formel:

= XLOOKUP (F2, A2: A15, B2: D15)

Ovenstående formel bruger et return_array -område, der er mere end en kolonne (B2: D15). Så når opslagsværdien findes i A2: A15, returnerer formlen hele rækken fra return_array.

Du kan heller ikke bare slette celler, der er en del af arrayet, der automatisk blev udfyldt. I dette eksempel kan du ikke slette H2 eller I2. Hvis du prøver, ville der ikke ske noget. Hvis du markerer disse celler, vil formlen i formellinjen være nedtonet (hvilket angiver, at den ikke kan ændres)

Du kan slette formlen i celle G2 (hvor vi oprindeligt indtastede den), den sletter hele resultatet.

Dette er en nyttig forbedring som tidligere med VLOOKUP, du bliver nødt til at angive kolonnummeret separat for hver formel.

Eksempel 3: Tovejsopslag ved hjælp af XLOOKUP (vandret og lodret opslag)

Nedenfor er et datasæt, hvor jeg vil vide Gregs score i matematik (emnet i celle G2).

Dette kan gøres ved hjælp af et tovejsopslag, hvor jeg leder efter navnet i kolonne A og emnets navn i række 1. Fordelen ved dette tovejsopslag er, at resultatet er uafhængigt af elevens navn på emnets navn. Hvis jeg ændrer emnets navn til kemi, ville denne tovejs XLOOKUP-formel stadig fungere og give mig det korrekte resultat.

Nedenfor er formlen, der udfører tovejsopslag og giver det korrekte resultat:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Denne formel bruger en indlejret XLOOKUP, hvor jeg først bruger den til at hente alle elevens mærker i celle F2.

Så resultatet af XLOOKUP (F2, A2: A15, B2: D15) er {21,94,81}, hvilket er en række mærker scoret af Greg i dette tilfælde.

Dette bruges derefter igen inden for den ydre XLOOKUP formel som return array. I den ydre XLOOKUP -formel leder jeg efter emnets navn (som er i celle G1), og opslags arrayet er B1: D1.

Hvis emnets navn er matematik, henter denne ydre XLOOKUP -formel den første værdi fra return arrayet - hvilket er {21,94,81} i dette eksempel.

Dette gør det samme, som indtil nu blev opnået ved hjælp af INDEX og MATCH -kombinationsboksen

Klik her for at downloade eksempelfilen og følge med

Eksempel 4: Når opslagsværdi ikke findes (fejlhåndtering)

Fejlhåndtering er nu tilføjet til XLOOKUP -formlen.

Det fjerde argument i XLOOKUP -funktionen er [if_not_found], hvor du kan angive, hvad du vil have, hvis opslaget ikke kan findes.

Antag, at du har datasættet som vist herunder, hvor du vil få matematikresultatet, hvis matchet, og hvis navnet ikke findes, vil du vende tilbage - 'Vises ikke'

Nedenstående formel gør dette:

= XLOOKUP (F2, A2: A15, B2: B15, "Vises ikke")

I dette tilfælde har jeg hårdkodet, hvad jeg vil have, hvis der ikke er nogen match. Du kan også bruge en cellereference til en celle eller en formel.

Eksempel 5: Indlejret XLOOKUP (opslag i flere områder)

Det geniale ved at have [if_not_found] argumentet er, at det giver dig mulighed for at bruge indlejret XLOOKUP formel.

Antag f.eks., At du har to separate lister som vist nedenfor. Selvom jeg har disse to tabeller på det samme ark, kan du have dem i separate ark eller endda projektmapper.

Nedenfor er den indlejrede XLOOKUP -formel, der kontrollerer navnet i begge tabeller og returnerer den tilsvarende værdi fra den angivne kolonne.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

I ovenstående formel har jeg brugt [if_not_found] -argumentet til at bruge en anden XLOOKUP -formel. Dette giver dig mulighed for at tilføje den anden XLOOKUP i den samme formel og scanne to tabeller med en enkelt formel.

Jeg er ikke sikker på, hvor mange indlejrede XLOOKUP'er du kan bruge i en formel. Jeg prøvede indtil 10, og det fungerede, så gav jeg op 🙂

Eksempel 6: Find den sidste matchende værdi

Denne var hårdt tiltrængt, og XLOOKUP gjorde dette muligt. Nu behøver du ikke finde indviklede måder at få den sidste matchende værdi i et område.

Antag, at du har datasættet som vist herunder, og du vil kontrollere, hvornår den sidste person blev ansat i hver afdeling, og hvad var lejedatoen.

Nedenstående formel søger efter den sidste værdi for hver afdeling og giver navnet på den sidste ansættelse:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

Og nedenstående formel giver lejedatoen for den sidste ansættelse for hver afdeling:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

Da XLOOKUP har en indbygget funktion til at angive opslagets retning (først til sidst eller sidst til først), gøres dette med en simpel formel. Med lodrette data ser VLOOKUP og INDEX/MATCH altid fra top til bund, men med XLOOKUP og kan også angive retningen som bund til top.

Eksempel 7: Omtrentlig match med XLOOKUP (Find skattesats)

En anden bemærkelsesværdig forbedring med XLOOKUP er, at der nu er fire matchtilstande (VLOOKUP har 2 og MATCH har 3).

Du kan angive et af de fire argumenter for at afgøre, hvordan opslagsværdien skal matches:

• 0 - Præcis match, hvor opslagsværdien nøjagtigt skal matche værdien i opslag_arrayet. Dette er standardindstillingen.
• -1 - Leder efter det nøjagtige match, men hvis det er fundet, returnerer det næste mindre element/værdi
• 1 - Leder efter det nøjagtige match, men hvis det er fundet, returnerer det næste større element/værdi
• 2 - For at foretage delvis matchning ved hjælp af jokertegn (* eller ~)

Men den bedste del er, at du ikke behøver at bekymre dig om, hvorvidt dine data er sorteret i stigende rækkefølge eller faldende rækkefølge. Selvom dataene ikke er sorteret, tager XLOOKUP sig af dem.

Nedenfor har jeg et datasæt, hvor jeg vil finde hver persons provision - og provisionen skal beregnes ved hjælp af tabellen til højre.

Nedenfor er formlen, der gør dette:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

Dette bruger simpelthen salgsværdien som opslag og kigger gennem opslagstabellen til højre. I denne formel har jeg brugt -1 som det femte argument ([match_mode]), hvilket betyder, at det vil lede efter et eksakt match, og når det ikke finder et, returnerer det værdien bare mindre end opslagsværdien .

Og som jeg sagde, behøver du ikke bekymre dig om, hvorvidt dine data ikke er sorteret.

Klik her for at downloade eksempelfilen og følge med

Eksempel 8: Horisontalt opslag

XLOOKUP kan udføre både lodret og vandret opslag.

Nedenfor har jeg et datasæt, hvor jeg har elevnavne og deres score i rækker, og jeg vil hente scoren for navnet i celle B7.

Nedenstående formel gør dette:

= XLOOKUP (B7, B1: O1, B2: O2)

Dette er intet andet end et simpelt opslag (ligner det, vi så i eksempel 1), men vandret.

Alle de eksempler, som jeg dækker om lodret opslag, kan også udføres med et vandret opslag ved hjælp af XLOOKUP (farvel til VLOOKUP og HLOOKUP).

Eksempel 9: Betinget opslag (Brug af XLOOKUP med andre formler)

Denne er et lidt avanceret eksempel, og viser også kraften i XLOOKUP, når du skal lave komplekse opslag.

Nedenfor er et datasæt, hvor jeg har navnene på eleverne og deres score, og jeg vil gerne kende navnet på den elev, der har scoret maksimum i hvert fag og antallet af elever, der har scoret mere end 80 i hvert fag.

Nedenfor er formlen, der giver navnet på den elev med de højeste karakterer i hvert emne:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Da XLOOKUP kan bruges til at returnere et helt array, har jeg brugt det til først at få alle mærker for det krævede emne.

For eksempel for matematik, når jeg bruger XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), giver det mig alle scoringer i matematik. Jeg kan derefter bruge MAX -funktionen til at finde den maksimale score i dette område.

Denne maksimale score bliver derefter min opslagsværdi, og opslagsområdet ville være arrayet returneret af XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Jeg bruger dette inden for en anden XLOOKUP -formel til at hente navnet på den elev, der har scoret de maksimale karakterer.

Og for at tælle antallet af elever, der har scoret mere end 80, skal du bruge nedenstående formel:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Denne bruger simpelthen XLOOKUP -formlen til at få et område med alle værdierne for det givne emne. Det ombryder det derefter i COUNTIF -funktionen for at få antallet af scoringer, der er mere end 80.

Eksempel 10: Brug af jokertegn i XLOOKUP

Ligesom du kan bruge jokertegn i VLOOKUP og MATCH, kan du også gøre dette med XLOOKUP.

Men der er en forskel.

I XLOOKUP skal du angive, at du bruger jokertegn (i det femte argument). Hvis du ikke angiver dette, giver XLOOKUP dig en fejl.

Nedenfor er et datasæt, hvor jeg har firmanavne og deres markedsværdi.

Jeg vil slå et firmanavn op i kolonne D og hente markedsværdi fra tabellen til venstre. Og da navnene i kolonne D ikke er nøjagtige matches, bliver jeg nødt til at bruge jokertegn.

Nedenfor er formlen, der gør dette:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

I ovenstående formel har jeg brugt stjernetegn (*) jokertegn før som efter D2 (det skal være inden for dobbelt anførselstegn og være forbundet med D2 ved hjælp af ampersand).

Dette fortæller formlen at se igennem alle cellerne, og hvis det indeholder ordet i celle D2 (som er Apple), betragtes det som et nøjagtigt match. Uanset hvor mange og hvilke tegn der er før og efter teksten i celle D2.

Og for at sikre, at XLOOKUP accepterer jokertegn, er det femte argument blevet sat til 2 (jokertegn match).

Eksempel 11: Find den sidste værdi i kolonnen

Da XLOOKUP giver dig mulighed for at søge fra bund til top, kan du let finde den sidste værdi på en liste samt hente den tilsvarende værdi fra en kolonne.

Antag, at du har et datasæt som vist nedenfor, og du vil vide, hvad det sidste selskab er, og hvad denne sidste virksomheds markedsværdi er.

Nedenstående formel giver dig navnet på den sidste virksomhed:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

Og nedenstående formel giver markedsværdien af ​​det sidste selskab på listen:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Disse formler bruger igen jokertegn. I disse har jeg brugt stjerne (*) som opslagsværdi, hvilket betyder, at dette ville betragte den første celle, den støder på, som en nøjagtig matchning (da stjerne kunne være et hvilket som helst tegn og et vilkårligt antal tegn).

Og da retningen er fra bund til top (for de lodret arrangerede data), returnerer den den sidste værdi på listen.

Og den anden formel siden bruger en separat return_range til at få markedsværdien af ​​efternavnet på listen.

Klik her for at downloade eksempelfilen og følge med

Hvad hvis du ikke har XLOOKUP?

Da XLOOKUP sandsynligvis kun er tilgængelig for Office 365 -brugere, er en måde at få det på at opgradere til Office 365.

Hvis du allerede har Office 365 Home, Personal eller University edition, har du allerede adgang til XLOOKUP. Alt du skal gøre er at deltage i Office Insider -programmet.

For at gøre dette skal du gå til fanen Filer, klikke på Konto og derefter klikke på indstillingen Office insider. Der ville være en mulighed for at deltage i insider -programmet.

Hvis du har andre Office 365 -abonnementer (f.eks. Enterprise), er jeg sikker på, at XLOOKUP og andre fantastiske funktioner (såsom dynamiske arrays, formler som SORT og FILTER) snart bliver tilgængelige.

Hvis du bruger Excel 2010/2013/2016/2019, har du ikke XLOOKUP, og du bliver nødt til at fortsætte med at bruge VLOOKUP, HLOOKUP og INDEX/MATCH combo for at få det bedste ud af opslagsformler.

XLOOKUP bagudkompatibilitet

Dette er en ting, du skal være forsigtig med - XLOOKUP er IKKE bagudkompatibel.

Det betyder, at hvis du opretter en fil og bruger XLOOKUP -formlen og derefter åbner den i en version, der ikke har XLOOKUP, viser den fejl.

Da XLOOKUP er et stort skridt fremad i den rigtige retning, tror jeg, at dette vil blive standardopslagsformlen, men det vil helt sikkert tage et par år, før det bliver bredt vedtaget. Jeg ser trods alt stadig nogle mennesker, der bruger Excel 2003.

Så det er 11 XLOOKUP -eksempler, der kan hjælpe dig med at udføre alle opslag og henvisninger, der er udført hurtigere og også gøre det let at bruge.

Håber du fandt denne vejledning nyttig!