Video: Excel XLOOKUP 2024
De mest populära i Excel 2016-uppslagsfunktionerna är HLOOKUP (för horisontell sökning) och VLOOKUP (för vertikal uppslag). Dessa funktioner finns i rullgardinsmenyn Sök och referens på fliken Formler i bandet och i kategorin Sök och referens i dialogrutan Infoga funktion. De är en del av en kraftfull grupp av funktioner som kan returnera värden genom att slå upp dem i datatabeller.
VLOOKUP-funktionen söker vertikalt (från topp till botten) längst till vänster i ett Lookup-bord tills programmet hittar ett värde som matchar eller överstiger det du tittar på. HLOOKUP-funktionen söker horisontellt (från vänster till höger) den översta raden i ett uppslagstabell tills den lokaliserar ett värde som överensstämmer med eller överstiger det du tittar på.
VLOOKUP-funktionen använder följande syntax:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Funktionen HLOOKUP följer den nästan identiska syntaxen:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) > I båda funktionerna är argumentet
lookup_value det värde som du vill leta upp i Lookup-tabellen, och table_array är cellintervallet eller namnet på Lookup-tabellen som innehåller både värde för att leta upp och det relaterade värdet att returnera.
col_index_num anger kolumnen i lookuptabellen som innehåller de värden som returneras av VLOOKUP-funktionen baserat på att matcha värdet av argumentet lookup_value mot de i argumentet table_array. Du bestämmer argumentet col_index_num som räknar hur många kolumner den här kolumnen är över till höger från den första kolumnen i det vertikala Lookup-tabellen, och du inkluderar den första kolumnen i Lookup-tabellen i denna räkning. Argumentet
row_index_num anger den rad som innehåller värdena returneras av HLOOKUP-funktionen i ett horisontellt bord. Du bestämmer argumentet row_index_num genom att räkna hur många rader i den här raden är från den övre raden i det horisontella uppslagstabellen. Återigen inkluderar du den övre raden i Lookup-tabellen i denna räkning. När du matar in
col_index_num eller row_index_num argumenten i VLOOKUP och HLOOKUP-funktionerna, överskrider värdet du entercannot det totala antalet kolumner eller rader i Lookup-tabellen. Argumentet
range_lookup i både VLOOKUP och HLOOKUP-funktionerna är det logiska SANT eller FALSK som anger om du vill att Excel ska hitta en exakt eller approximativ match för lookup_value i table_array.När du anger TRUE eller släpper argumentet range_lookup i VLOOKUP eller HLOOKUP-funktionen, hittar Excel en ungefärlig matchning. När du anger FALSE som argumentet range_lookup , hittar Excel bara exakta träffar. Hitta approximativa matchningar gäller endast när du tittar upp numeriska poster (i stället för text) i den första kolumnen eller raden i vertikalt eller horisontellt uppslagstabell. När Excel inte hittar en exakt matchning i kolumnen eller raden Sökning, lokaliserar den nästa högsta värde som inte överstiger argumentet
lookup_value och returnerar sedan värdet i kolumnen eller raden som anges av < kolumnindex eller radindex argument. När du använder VLOOKUP och HLOOKUP-funktionerna måste text eller numeriska poster i kolumnen Kolla eller rad (det vill säga den vänstra kolumnen i ett vertikalt uppslagstabell eller den övre raden i ett horisontellt uppslagstabell) vara unika. Dessa poster måste också ordnas eller sorteras i stigande ordning. det vill säga alfabetisk ordning för textposter och lägsta till högsta order för numeriska poster. Figuren visar ett exempel på att använda VLOOKUP-funktionen för att returnera antingen en 15% eller 20% -tips från ett tipsbord, beroende på kontrollens förhandskontroll. Cell F3 innehåller VLOOKUP-funktionen:
= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0, 15, 2, 3))
Denna formel returnerar spetsens mängd baserat på tipsprocenten i cell F1 och Förebeloppet av kontrollen i cell F2.
Använd VLOOKUP-funktionen för att returnera mängden spets som ska läggas till från en sökningstabell.
För att använda detta tipsbord anger du procenten av spetsen (15% eller 20%) i cell F1 (namnet Tip_Percentage) och mängden av kontrollen före skatt i cell F2 (namnet Pretax_Total). Excel ser upp det värde som du anger i Pretax_Total-cellen i den första kolumnen i Lookup-tabellen, som innehåller cellintervallet A2: C101 och heter Tip_Table.
Excel flyttar sedan ner värdena i den första kolumnen i Tip_Table tills den hittar en matchning, varefter programmet använder argumentetcol_index_num
i VLOOKUP-funktionen för att bestämma vilket tipsbelopp från den raden i tabellen till återgå till cell F3. Om Excel finner att värdet som anges i Pretax_Total-cellen ($ 16, 50 i det här exemplet) inte exakt matchar något av värdena i den första kolumnen i Tip_Table fortsätter programmet att söka ner jämförelseområdet tills det möter det första värdet som överskrider pretax total (17 00 i cell A19 i detta exempel). Excel flyttar sedan tillbaka till föregående rad i tabellen och returnerar värdet i kolumnen som matchar argumentet col_index_num i VLOOKUP-funktionen. (Detta beror på att valfri range_lookup -argumentet har utelämnats från funktionen.) Observera att exemplet i tiptabellen i figuren använder en IF-funktion för att bestämma argumentet col_index_num
för VLOOKUP-funktionen i cell F3. IF-funktionen bestämmer kolonnnumret som ska användas i speltabellen genom att matcha procentandelen som anges i Tip_Percentage (cell F1) med 0.15. Om de matchar returnerar funktionen 2 som argumentet col_index_num och VLOOKUP-funktionen returnerar ett värde från den andra kolumnen (15% kolumnen B) i Tip_Table-intervallet. Annars returnerar IF-funktionen 3 som argumentet col_index_num och VLOOKUP-funktionen returnerar ett värde från den tredje kolumnen (20% -kolumnen C) i Tip_Table-området. Följande bild visar ett exempel som använder HLOOKUP-funktionen för att leta upp priset på varje bakverk som är lagrat i ett separat prisuppslagstabell och sedan returnera det priset till pris / doz-kolumnen i dagslistan. Cell F3 innehåller den ursprungliga formeln med HLOOKUP-funktionen som sedan kopieras ner kolumn F: Med hjälp av HLOOKUP-funktionen returnerar du priset på ett bakverk från ett uppslagstabell.
= HLOOKUP (item, Price_table, 2, FALSE)
I denna HLOOKUP-funktion definieras det intervallnamn, som anges i kolumnen Item i intervallet C3: C62, som argumentetlookup_value
och cellintervallnamnet Pristabell som ges till cellintervallet I1: M2 är argumentet table_array . Argumentet row_index_num är att du vill att Excel ska returnera priserna i den andra raden i prisuppslagstabellen, och det valfria argumentet range_lookup är FALSK eftersom artikelnamnet i den dagliga försäljningen listan måste matcha exakt föremålet i tabellen Prisuppsättning. Genom att använda HLOOKUP-funktionen använder du prisklassintervallet för att mata in priset per dussin för varje bagagevarupost i listan Daglig försäljning, vilket gör det mycket enkelt att uppdatera någon av försäljningarna i listan. Allt du behöver göra är att ändra sin pris / Doz-kostnad i det här intervallet, och HLOOKUP-funktionen uppdaterar omedelbart det nya priset i den dagliga försäljningslistan varhelst objektet säljs.