1. SoftwareMicrosoft OfficeExcel Slik bruker du XLOOKUP-funksjonen i Excel 2016

Av Greg Harvey

Excel 2016 for Office 365-abonnenter på Windows og Mac støtter nå en ny XLOOKUP-funksjon, spionert som en betydelig enklere og mer allsidig erstatning for den veldig populære (men ofte mislykkede) vertikale oppslagfunksjonen VLOOKUP (vet ikke hva X i XLOOKUP står for; eXtensive, kanskje?).

For de av dere som ennå ikke er kjent med VLOOKUP (regnet som den tredje mest brukte funksjonen rett etter SUM og AVERAGE), søker denne funksjonen loddrett etter rad i den venstre kolonnen i en utpekt oppslagstabell fra topp til bunn til den finner en verdi i en oppslagskolonne angitt med et forskjøvet nummer som samsvarer med eller overskrider det du ser opp. Selv om det er veldig nyttig for å finne bestemte elementer i en lang liste eller kolonne i en datatabell i arbeidsarket, har VLOOKUP-funksjonen flere begrensninger som ikke deles av denne nye oppslagfunksjonen, som XLOOKUP:

  • Som standard er det å finne nøyaktige treff for din oppslagsverdi i oppslagområdet Kan søke både vertikalt (etter rad) og horisontalt (etter kolonne) i en tabell, og dermed erstatte behovet for å bruke HLOOKUP-funksjonen når du søker horisontalt etter kolonne Kan søke til venstre eller høyre slik at oppslagområdet i oppslagstabellen 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 standard nøyaktig samsvar brukes, fungerer den selv når verdier i oppslagområdet ikke er sortert i bestemt rekkefølge Kan søke fra den nederste raden til toppen i søkefeltet med et valgfritt søkemodusargument

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

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

Det nødvendige lookup_value-argumentet angir verdien eller elementet du søker etter. Det nødvendige oppstillingsargumentet angir utvalget av celler som skal søkes etter denne oppslagsverdien, og return_array-argumentet utpeker området med celler som inneholder verdien du vil returnere når Excel finner en nøyaktig samsvar.

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

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

  • 0 for en nøyaktig kamp (standard, samme som når ingen match_mode-argument er utpekt) -1 for eksakt samsvar eller neste mindre verdi 1 for nøyaktig samsvar eller neste større verdi 2 for delvis samsvar med jokertegn som er knyttet til cellehenvisning i argumentet lookup_value

Det valgfrie search_mode-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, det samme som når ikke noe search_mode-argument er utpekt) -1 for å søke sist til første, 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 arrangert etter land. For å bruke XLOOKUP for å returnere det totale salget fra denne tabellen i celle E4 basert på landet du oppgir i celle D4 i regnearket, tar du disse trinnene:

  1. Plasser cellemarkøren i celle E4 på arbeidsarket Klikk på alternativet Oppslag og referanse i Formler-fanen etterfulgt av XLOOKUP nær bunnen av rullegardinmenyen for å åpne dialogboksen Funksjonsargumenter. Klikk celle D4 i arbeidsarket for å legge inn cellereferansen i tekstfeltet Lookup_value. Trykk Tab for å velge tekstfeltet Lookup_array, og klikk deretter celle A4 og hold Shift nede mens du trykker Ctrl-pil ned for å velge A4: A8 som området som skal søkes (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 tekstboks, klikk deretter celle B4 og hold Shift nede mens du trykker Ctrl-pil ned for å velge B4: B8 som området som inneholder verdiene som skal returneres basert på resultatene fra søket (som vises som Tabell1 [Totalt salg] i tekstboksen).

Klikk OK for å gå inn i XLOOKUP-formelen i celle E4.

Opprettelse av XLOOKUP-formel

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

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

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

XLOOKUP formel i D4

Fordi XLOOKUP-funksjonen er like behagelig å søke horisontalt etter kolonne som den søker loddrett 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 tabellen for produksjonsplan for 2019 for delenummer, AB-100 til AB-103 for månedene april til desember, viser deg hvordan dette gjøres.

nestede XLOOKUP-funksjoner

I celle B12 opprettet jeg følgende formel:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Denne formelen begynner med å definere en XLOOKUP-funksjon som vertikalt søker etter rad etter en eksakt samsvar med deloppføringen som er laget i cellen som heter 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 å få et nøyaktig samsvar med datooppføringen gjort i cellen som heter date_lookup (celle B11, i dette tilfellet). Return_array-argumentet for denne andre, nestede XLOOKUP-funksjonen er $ B $ 3: $ J $ 6, celleområdet for alle produksjonsverdier i tabellen.

Slik formelen fungerer 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ådet D3: D6 i Jun-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 loddrett søk etter rad for en nøyaktig samsvar med delenummeroppføringen som er laget i celle B11 (kalt part_lookup). Fordi i dette eksemplet denne delen_lookup-cellen inneholder AB-102, returnerer formelen bare produksjonsverdien Jun-19, 306, fra resultatet av den andre neste XLOOKUP-funksjonen.

Der har du det! Et første blikk på XLOOKUP, en kraftig, allsidig og ganske brukervennlig ny oppslagsfunksjon som ikke bare kan utføre oppslag av en verdi utført av VLOOKUP og HLOOKUP-funksjonene, men også oppslagene til toveisverdi utført ved å kombinere INDEX og MATCH fungerer også.

* Dessverre er ikke XLOOKUP-funksjonen bakoverkompatibel med tidligere versjoner av Microsoft Excel som bare støtter VLOOKUP- og HLOOKUP-funksjonene eller kompatible med nåværende versjoner som ennå ikke inkluderer den som en av oppslagfunksjonene deres, for eksempel Excel 2019 og Excel Online. Dette betyr at hvis du deler en arbeidsbok som inneholder XLOOKUP-formler med medarbeidere eller klienter som bruker en versjon av Excel som ikke inkluderer denne nye oppslagfunksjonen, vil alle disse formlene returnere #NAME? feilverdier når de åpner regnearket.