De mest populære af opslagsfunktionerne i Excel 2013 er funktionerne HLOOKUP (til horisontalt opslag) og VLOOKUP (til lodret opslag). Disse funktioner er placeret på rullemenuen Opslag og reference på fanen Formler på båndet samt i kategorien Opslag og reference i dialogboksen Indsæt funktion.
De er en del af en kraftfuld gruppe af funktioner, der kan returnere værdier ved at slå dem op i datatabeller.
VLOOKUP-funktionen søger lodret (fra top til bund) i kolonnen længst til venstre i en opslagstabel, indtil programmet finder en værdi, der matcher eller overstiger den, du slår op. HOPSLAG-funktionen søger vandret (fra venstre mod højre) den øverste række i en opslagstabel, indtil den finder en værdi, der matcher eller overstiger den, du slår op.
Funktionen VLOOKUP bruger følgende syntaks:
VOPSLAG(opslagsværdi, tabelmatrix, kolonneindeks_tal,[områdeopslag])
HLOOKUP-funktionen følger den næsten identiske syntaks:
HOPSLAG(opslagsværdi,tabelmatrix,rækkeindekstal,[områdeopslag])
I begge funktioner er opslagsværdi- argumentet den værdi, du vil slå op i opslagstabellen, og tabelmatrix er celleområdet eller navnet på opslagstabellen, der både indeholder den værdi, der skal slås op, og den relaterede værdi, der skal returneres.
Den col_index_num argument i LOPSLAG funktion er nummeret på den kolonne, hvis værdier sammenlignes med opslagsværdi argument i en lodret tabel. Den row_index_num argument i VOPSLAG funktion er antallet af rækken, hvis værdier sammenlignes med opslagsværdi i et vandret bord.
Når du indtaster argumenterne col_index_num eller row_index_num i funktionerne VLOOKUP og HLOOKUP , skal du indtaste en værdi større end nul, som ikke overstiger det samlede antal kolonner eller rækker i opslagstabellen.
Det valgfri range_lookup- argument i både VLOOKUP- og HLOOKUP-funktionerne er det logiske SAND eller FALSK, der angiver, om du ønsker, at Excel skal finde et nøjagtigt eller omtrentligt match for opslagsværdien i tabelmatrixen.
Når du angiver TRUE eller udelader range_lookup- argumentet i funktionen VLOOKUP eller HLOOKUP, finder Excel et omtrentligt match. Når du angiver FALSE som range_lookup- argumentet, finder Excel kun eksakte matches.
At finde omtrentlige overensstemmelser gælder kun, når du slår numeriske poster op (i stedet for tekst) i den første kolonne eller række i den lodrette eller vandrette opslagstabel. Når Excel ikke finder et nøjagtigt match i denne opslagskolonne eller -række, finder den den næsthøjeste værdi, der ikke overstiger opslagsværdi- argumentet, og returnerer derefter værdien i kolonnen eller rækken, der er angivet af argumenterne col_index_num eller row_index_num .
Når du bruger funktionerne OPSLAG og OPSLAG, skal teksten eller numeriske indtastninger i opslagskolonnen eller rækken (det vil sige kolonnen længst til venstre i en lodret opslagstabel eller den øverste række i en vandret opslagstabel) være unik. Disse poster skal også arrangeres eller sorteres i stigende rækkefølge; det vil sige alfabetisk rækkefølge for tekstindtastninger og laveste til højeste rækkefølge for numeriske indtastninger.
Figuren viser et eksempel på brug af VLOOKUP-funktionen til at returnere enten 15 % eller 20 % drikkepenge fra en drikkepenge-tabel, afhængigt af checkens total før skat. Celle F3 indeholder funktionen VLOOKUP:
=VOPSLAG(Førtax_Total,Tip_Table,IF(Tip_Percentage=0,15,2,3))
Denne formel returnerer mængden af drikkepenge baseret på drikkepengeprocenten i celle F1 og beløbet før skat af checken i celle F2.
For at bruge denne tiptabel skal du indtaste procentdelen af drikkepengene (15 % eller 20 %) i celle F1 (navngivet Tip_Percentage) og beløbet for checken før skat i celle F2 (navngivet Pretax_Total). Excel slår derefter den værdi op, som du indtaster i Pretax_Total-cellen i den første kolonne i opslagstabellen, som inkluderer celleområdet A2:C101 og hedder Tip_Table.
Excel flytter derefter værdierne ned i den første kolonne i Tip_Table, indtil det finder et match, hvorefter programmet bruger argumentet col_index_num i funktionen VLOOKUP til at bestemme, hvilket tipbeløb fra den række i tabellen, der skal vende tilbage til celle F3.
Hvis Excel opdager, at værdien, der er indtastet i Pretax_Total-cellen ($16,50 i dette eksempel) ikke nøjagtigt matcher en af værdierne i den første kolonne i Tip_Table, fortsætter programmet med at søge ned i sammenligningsområdet, indtil det støder på den første værdi, der overstiger totalen før skat (17,00 i celle A19 i dette eksempel).
Excel flytter derefter tilbage til den forrige række i tabellen og returnerer den værdi i kolonnen, der matcher argumentet col_index_num for funktionen VLOOKUP . (Dette skyldes, at det valgfri range_lookup- argument er blevet udeladt fra funktionen.)
Bemærk, at tiptabeleksemplet bruger en HVIS-funktion til at bestemme col_index_num- argumentet for funktionen VLOOKUP i celle F3.
HVIS-funktionen bestemmer kolonnenummeret, der skal bruges i tiptabellen, ved at matche procentdelen, der er indtastet i Tip_Percentage (celle F1) med 0,15. Hvis de matcher, returnerer funktionen 2 som argumentet col_index_num, og funktionen VLOOKUP returnerer en værdi fra den anden kolonne (15 % kolonne B) i Tip_Table-området.
Ellers returnerer IF-funktionen 3 som col_index_num- argumentet, og VLOOKUP- funktionen returnerer en værdi fra den tredje kolonne (20 %-kolonnen C) i Tip_Table-området.
Følgende figur viser et eksempel, der bruger funktionen HOPSLAG til at slå prisen op på hver bagerivare, der er gemt i en separat prisopslagstabel, og derefter returnere denne pris til kolonnen Pris/Doz på listen Dagligt salg. Celle F3 indeholder den originale formel med funktionen HOPSLAG, der derefter kopieres ned i kolonne F:
=HOPSLAG(vare;Pristabel;2;FALSK)
I denne HOPSLAG-funktion er områdenavnet Item, der er givet til kolonnen Item i området C3:C62, defineret som opslagsværdi- argumentet, og celleområdenavnet Pristabellen, der er givet til celleområdet I1:M2, er argumentet table_array .
Den row_index_num argument er 2, fordi du vil have Excel til at returnere de priser i den anden række af priser Opslag bord, og den valgfri range_lookup argument er FALSK, fordi elementet navn i Daily Sales liste skal svare præcis det element navn i Priserne Opslag bord .
Ved at få funktionen HOPSLAG til at bruge pristabelområdet til at indtaste prisen pr. dusin for hver bagerivarevare i den daglige salgsliste, gør du det til en meget enkel sag at opdatere ethvert af salgene på listen.
Alt du skal gøre er at ændre dens Pris/Doz-omkostninger i dette interval, og HOPSLAG-funktionen opdaterer straks den nye pris i den daglige salgsliste, uanset hvor varen sælges.