De mest populære av Excel 2016-oppslagsfunksjonene er funksjonene HLOOKUP (for horisontalt oppslag) og VLOOKUP (for vertikalt oppslag). Disse funksjonene er plassert på rullegardinmenyen Oppslag og referanse i kategorien Formler på båndet, samt i kategorien Oppslag og referanse i dialogboksen Sett inn funksjon. De er en del av en kraftig gruppe funksjoner som kan returnere verdier ved å slå dem opp i datatabeller.
VLOOKUP-funksjonen søker vertikalt (fra topp til bunn) kolonnen lengst til venstre i en oppslagstabell til programmet finner en verdi som samsvarer med eller overstiger den du ser opp. HLOOKUP-funksjonen søker horisontalt (fra venstre til høyre) den øverste raden i en oppslagstabell til den finner en verdi som samsvarer med eller overstiger den du ser opp.
VLOOKUP-funksjonen bruker følgende syntaks:
VLOOKUP(oppslagsverdi;tabellmatrise;kol.indeksnummer;[områdeoppslag])
HLOOKUP-funksjonen følger den nesten identiske syntaksen:
HOPPSLAKK(oppslagsverdi;tabellmatrise;radindeksnummer;[områdeoppslag])
I begge funksjonene er oppslagsverdi- argumentet verdien du vil slå opp i oppslagstabellen, og table_array er celleområdet eller navnet på oppslagstabellen som inneholder både verdien som skal slås opp og den relaterte verdien som skal returneres.
Den col_index_num argumentet betegner kolonnen i oppslagstabellen som inneholder verdiene som returneres av VLOOKUP funksjon basert på å sammenholde verdien av lookup_value argument mot de i tabell argumentet. Du bestemmer col_index_num- argumentet som teller hvor mange kolonner denne kolonnen er over til høyre fra den første kolonnen i den vertikale oppslagstabellen, og du inkluderer den første kolonnen i oppslagstabellen i dette antallet.
Den row_index_num argumentet betegner den rad som inneholder verdiene blir returnert ved HLOOKUP i et horisontalt bord. Du bestemmer row_index_num- argumentet ved å telle hvor mange rader nedover denne raden er fra den øverste raden i den horisontale oppslagstabellen. Igjen inkluderer du den øverste raden i oppslagstabellen i denne tellingen.
Når du skriver inn argumentene col_index_num eller row_index_num i funksjonene VLOOKUP og HLOOKUP, kan ikke verdien du skriver inn overstige det totale antallet kolonner eller rader i oppslagstabellen.
Den valgfrie range_lookup argument i både FINN.RAD og FINN.KOLONNE funksjonene er logisk SANN eller USANN som angir om du vil Excel til å finne en eksakt eller omtrentlig samsvar for søkeverdi i tabell. Når du spesifiserer TRUE eller utelater argumentet range_lookup i funksjonen VLOOKUP eller HLOOKUP, finner Excel et omtrentlig samsvar. Når du angir FALSE som range_lookup- argumentet, finner Excel bare eksakte treff.
Å finne omtrentlige treff gjelder bare når du slår opp numeriske oppføringer (i stedet for tekst) i den første kolonnen eller raden i den vertikale eller horisontale oppslagstabellen. Når Excel ikke finner et eksakt samsvar i denne oppslagskolonnen eller -raden, finner den den nest høyeste verdien som ikke overskrider oppslagsverdi- argumentet, og returnerer deretter verdien i kolonnen eller raden angitt av argumentene col_index_num eller row_index_num .
Når du bruker funksjonene VLOOKUP og HLOOKUP, må teksten eller numeriske oppføringer i oppslagskolonnen eller raden (det vil si kolonnen lengst til venstre i en vertikal oppslagstabell eller den øverste raden i en horisontal oppslagstabell) være unik. Disse oppføringene må også ordnes eller sorteres i stigende rekkefølge; det vil si alfabetisk rekkefølge for tekstoppføringer, og laveste til høyeste rekkefølge for numeriske oppføringer.
Figuren viser et eksempel på bruk av VLOOKUP-funksjonen for å returnere enten 15 % eller 20 % tips fra en tipstabell, avhengig av totalen før skatt for sjekken. Celle F3 inneholder funksjonen VLOOKUP:
=VLOOKUP(Pretax_Total,Tips_Table,IF(Tips_Prosent=0,15,2,3))
Denne formelen returnerer tipsbeløpet basert på tipsprosenten i celle F1 og beløpet før skatt for sjekken i celle F2.
Ved å bruke VLOOKUP-funksjonen for å returnere mengden av tipset som skal legges til fra en oppslagstabell.
For å bruke denne tipstabellen, skriv inn prosentandelen av tipset (15 % eller 20 %) i celle F1 (kalt Tips_Percentage) og beløpet på sjekken før skatt i celle F2 (kalt Pretax_Total). Excel slår deretter opp verdien du angir i Pretax_Total-cellen i den første kolonnen i oppslagstabellen, som inkluderer celleområdet A2:C101 og heter Tips_Table.
Excel flytter deretter ned verdiene i den første kolonnen i Tip_Table til den finner en match, hvorpå programmet bruker argumentet col_index_num i VLOOKUP-funksjonen for å bestemme hvilket tipsbeløp fra den raden i tabellen som skal gå tilbake til celle F3. Hvis Excel finner at verdien angitt i Pretax_Total-cellen ($16,50 i dette eksemplet) ikke samsvarer nøyaktig med en av verdiene i den første kolonnen i Tip_Table, fortsetter programmet å søke nedover sammenligningsområdet til det finner den første verdien som overskrider totalen før skatt (17,00 i celle A19 i dette eksemplet). Excel flytter deretter tilbake til forrige rad i tabellen og returnerer verdien i kolonnen som samsvarer med col_index_num- argumentet til VLOOKUP-funksjonen. (Dette er fordi den valgfrie range_lookupargumentet er utelatt fra funksjonen.)
Merk at tipstabelleksemplet i figuren bruker en HVIS-funksjon for å bestemme col_index_num- argumentet for VLOOKUP-funksjonen i celle F3. HVIS-funksjonen bestemmer kolonnenummeret som skal brukes i tipstabellen ved å matche prosenten angitt i Tips_Prosent (celle F1) med 0,15. Hvis de samsvarer, returnerer funksjonen 2 som col_index_num- argumentet og VLOOKUP-funksjonen returnerer en verdi fra den andre kolonnen (15 %-kolonnen B) i Tips_Table-området. Ellers returnerer HVIS-funksjonen 3 som col_index_num- argumentet og VLOOKUP-funksjonen returnerer en verdi fra den tredje kolonnen (20 %-kolonnen C) i Tips_Table-området.
Den følgende figuren viser et eksempel som bruker HOPPSLAKK-funksjonen til å slå opp prisen på hver bakervare som er lagret i en separat prisoppslagstabell og deretter returnere den prisen til Pris/Doz-kolonnen i Daglig salg-listen. Celle F3 inneholder den opprinnelige formelen med HLOOKUP-funksjonen som deretter kopieres ned i kolonne F:
Bruke HOPPLINGS-funksjonen til å returnere prisen på en bakervare fra en oppslagstabell.
=HOPPLINN(vare;Pristabell;2;USANN)
I denne OPPSLAG-funksjonen er områdenavnet Element som er gitt til Element-kolonnen i området C3:C62 definert som oppslagsverdi- argumentet, og celleområdenavnet Pristabellen som er gitt til celleområdet I1:M2 er argumentet table_array . Den row_index_num argumentet er to fordi du vil Excel å returnere prisene i andre rad av prisene Oppslag bordet, og den valgfrie range_lookup argumentet er falsk fordi elementnavnet i Daily Sales listen må samsvare nøyaktig navnet element i prisene oppslagstabellen .
Ved å la HOPPLINGS-funksjonen bruke pristabellområdet til å legge inn prisen per dusin for hver bakervarevare i dagligsalgslisten, gjør du det til en veldig enkel sak å oppdatere alle salgene i listen. Alt du trenger å gjøre er å endre Pris/Doz-kostnaden i dette området, og HLOOKUP-funksjonen oppdaterer umiddelbart den nye prisen i den daglige salgslisten uansett hvor varen selges.