Slik bruker du XLOOKUP-funksjonen i Excel 2016

Excel 2016 for Office 365-abonnenter på Windows og Mac støtter nå en ny XLOOKUP-funksjon, utpekt som en betydelig enklere og mer allsidig erstatning for den svært populære (men ofte utskjelte) vertikale oppslagsfunksjonen VLOOKUP (vet ikke hva X-en i XLOOKUP er står for; omfattende, kanskje?).

For de av dere som ennå ikke er kjent med VLOOKUP (regnes som den tredje mest brukte funksjonen rett etter SUM og AVERAGE), søker denne funksjonen vertikalt for rad i kolonnen lengst til venstre i en angitt oppslagstabell fra topp til bunn til den finner en verdi i en oppslagskolonne angitt med et offsetnummer som samsvarer med eller overstiger det du slår opp. Selv om den er enormt nyttig for å finne bestemte elementer i en lang liste eller kolonne i en datatabell i regnearket, har VLOOKUP-funksjonen flere begrensninger som ikke deles av denne nye oppslagsfunksjonen, som XLOOKUP:

• Standard for å finne eksakte treff for oppslagsverdien din i oppslagsområdet
• Kan søke både vertikalt (etter rad) og horisontalt (etter kolonne) i en tabell, og erstatter dermed behovet for å bruke HLOOKUP-funksjonen når du søker horisontalt etter kolonne
• Kan søke til venstre eller høyre slik at oppslagsområdet i oppslagstabellen din ikke trenger å være plassert i en kolonne til venstre for den som er angitt som returområde for at funksjonen skal fungere
• Når standarden for eksakt samsvar brukes, fungerer selv når verdiene i oppslagsområdet ikke er sortert i spesiell rekkefølge
• Kan søke fra den nederste raden til toppen i oppslagsmatriseområdet, ved å bruke et valgfritt søkemodusargument

XLOOKUP-funksjonen har fem mulige argumenter, hvorav de tre første er obligatoriske og de to siste valgfrie, ved hjelp av følgende syntaks:

XLOOKUP ( søkeverdi , matrise , return_array , [ match_mode ], [ search_mode ])

Det nødvendige oppslagsverdi- argumentet angir verdien eller elementet du søker etter. Det påkrevde oppslagsmatriseargumentet angir celleområdet som skal søkes etter denne oppslagsverdien, og return_array- argumentet angir celleområdet som inneholder verdien du vil ha returnert når Excel finner et eksakt samsvar.

* Husk når du utpeker lookup_array og return_array-argumentene i XLOOKUP-funksjonen, at begge områdene må være like lange, ellers vil Excel returnere #VALUE! feil i formelen din. Dette er enda mer grunnen til at du bruker områdenavn eller kolonnenavn til en utpekt datatabell når du definerer disse argumentene i stedet for å peke på dem eller skrive inn cellereferansene deres .

Det valgfrie match_mode- argumentet kan inneholde en av følgende fire verdier:

• 0 for et eksakt samsvar (standard, samme som når ingen match_mode- argument er angitt)
• -1 for eksakt samsvar eller neste lavere verdi
• 1 for eksakt samsvar eller neste større verdi
• 2 for delvis samsvar ved bruk av jokertegn koblet til cellereferanse i lookup_value- argumentet

Det valgfrie søkemodus- argumentet kan inneholde en av følgende fire verdier:

• 1 for å søke først til sist, det vil si fra topp til bunn (standard, samme som når ingen søkemodus- argument er angitt)
• -1 for å søke sist-til-først, det vil si bunn til topp
• 2 for et binært søk i stigende rekkefølge
• -2 for binært søk i synkende rekkefølge

Den beste måten å forstå kraften og allsidigheten til den nye XLOOKUP-funksjonen er å se den i aksjon i et Excel-regneark. I den følgende figuren har jeg et regneark med en enkel salgsdatatabell for 2019 ordnet etter land. For å bruke XLOOKUP til å returnere det totale salget fra denne tabellen i celle E4 basert på landet du angir i celle D4 i regnearket, tar du disse trinnene:

Plasser cellemarkøren i celle E4 i regnearket

Klikk på alternativet Oppslag og referanse på Formler-fanen etterfulgt av XLOOKUP nær bunnen av rullegardinmenyen for å åpne dialogboksen Funksjonsargumenter.

Klikk celle D4 i regnearket for å angi cellereferansen i tekstboksen Lookup_value-argument.

Trykk Tab for å velge tekstboksen Lookup_array-argument, klikk deretter celle A4 og hold nede Shift mens du trykker på Ctrl-ned-pil for å velge A4:A8 som området du vil søke etter (fordi området A3:B8 er definert som en Excel-datatabell, Tabell1[Land] vises i tekstboksen i stedet for området A4:A8).

Trykk Tab for å velge Return_array argument tekstboksen, klikk deretter celle B4 og hold nede Shift mens du trykker Ctrl-ned pil for å velge B4:B8 som området som inneholder verdiene som skal returneres basert på resultatene av søket (som vises som Tabell1[Totalt salg] i tekstboksen).

Klikk OK for å angi XLOOKUP-formelen i celle E4.

Opprette en formel med XLOOKUP i celle E4 som returnerer salg basert på landet angitt i celle D4.

Excel legger inn XLOOKUP-formelen i celle E4 i regnearket og returnerer 4900 som resultat fordi Costa Rica for øyeblikket er lagt inn i oppslagscellen D4 og som du kan se i salgstabellen for 2019, er dette faktisk det totale salget for dette landet.

Fordi XLOOKUP fungerer like godt fra høyre til venstre som fra venstre til høyre, kan du like godt bruke denne funksjonen til å returnere landet fra denne salgstabellen basert på et bestemt salgstall. Følgende figur viser deg hvordan du gjør dette. Denne gangen oppretter du XLOOKUP-formelen i celle D4 og angir verdien angitt i celle E4 (11 000, i dette tilfellet) som lookup_value-argumentet.

I tillegg skriver du inn -1 som match_mode-argumentet for å overstyre funksjonens eksakte samsvarsstandard slik at Excel returnerer landet med eksakt samsvar til salgsverdien angitt i oppslagscellen E4 eller den med nest lavere totale salg (Mexico med $10 000 i dette tilfellet, siden det ikke er noe land i denne tabellen med $11 000 av totalt salg). Uten å angi et match_mode-argument for denne formelen, ville Excel returnert #NA som resultat, fordi det ikke er noe eksakt samsvar til $11 000 i denne salgstabellen.

Opprette en formel med XLOOKUP i celle D4 som returnerer landet basert på salget angitt i celle E4

Fordi XLOOKUP-funksjonen er like komfortabel å søke horisontalt etter kolonne som den søker vertikalt etter rad, kan du bruke den til å lage en formel som utfører et toveis oppslag (erstatter behovet for å lage en formel som kombinerer INDEX- og MATCH-funksjonene som i fortiden). Følgende figur, som inneholder produksjonsplantabellen for 2019 for delenummer, AB-100 til og med AB-103 for månedene april til desember, viser deg hvordan dette gjøres.

Opprette en formel med nestede XLOOKUP-funksjoner for å returnere antall enheter produsert for en del i en bestemt måned

I celle B12 opprettet jeg følgende formel:

=XLOOKUP(deloppslag,$A$3:$A$6,XLOOKUP(datooppslag,$B$2:$J$2,$B$3:$J$6))

Denne formelen begynner med å definere en XLOOKUP-funksjon som vertikalt søker etter rad for en eksakt samsvar med deloppføringen som er gjort i cellen med navnet part_lookup (celle B10, i dette tilfellet) i celleområdet $A$3:$A$6 i produksjonstabellen . Vær imidlertid oppmerksom på at return_array-argumentet for denne opprinnelige LOOKUP-funksjonen i seg selv er en andre XLOOKUP-funksjon.

Denne andre, nestede XLOOKUP-funksjonen søker i celleområdet $B$2:$J$2 horisontalt etter kolonne for en nøyaktig overensstemmelse med datooppføringen i cellen kalt date_lookup (celle B11, i dette tilfellet). Argumentet return_array for denne andre, nestede XLOOKUP-funksjonen er $B$3:$J$6, celleområdet for alle produksjonsverdier i tabellen.

Måten denne formelen fungerer på er at Excel først beregner resultatet av den andre, nestede XLOOKUP-funksjonen ved å utføre et horisontalt søk som, i dette tilfellet, returnerer matrisen i celleområde D3: D6 i juni-19-kolonnen (med verdiene: 438, 153, 306 og 779) som resultat. Dette resultatet blir på sin side return_array-argumentet for den opprinnelige XLOOKUP-funksjonen som utfører et vertikalt søk etter rad for en eksakt samsvar med delenummeroppføringen i celle B11 (kalt part_lookup). Fordi, i dette eksemplet, inneholder denne part_lookup-cellen AB-102, returnerer formelen kun produksjonsverdien Jun-19, 306, fra resultatet av den andre, neste XLOOKUP-funksjonen.

Der har du det! En første titt på XLOOKUP, en kraftig, allsidig og ganske brukervennlig ny oppslagsfunksjon som ikke bare kan utføre enkeltverdioppslag utført av funksjonene VLOOKUP og HLOOKUP, men også toveis verdioppslag utført ved å kombinere INDEX og MATCH funksjoner også.

* Dessverre er XLOOKUP-funksjonen ikke bakoverkompatibel med tidligere versjoner av Microsoft Excel som kun støtter funksjonene VLOOKUP og HLOOKUP eller kompatibel med gjeldende versjoner som ennå ikke inkluderer den som en av oppslagsfunksjonene deres, for eksempel Excel 2019 og Excel Online . Dette betyr at hvis du deler en arbeidsbok som inneholder XLOOKUP-formler med kolleger eller klienter som bruker en versjon av Excel som ikke inkluderer denne nye oppslagsfunksjonen, vil alle disse formlene returnere #NAVN? feilverdier når de åpner regnearket.