Genom att kombinera Excels VLOOKUP-funktion med funktionen COLUMN kan du skapa en uppslagsformel som returnerar flera värden från en enda rad i en databas eller datatabell. Lär dig hur du skapar en uppslagsformel som returnerar flera värden från en enda datapost.
Instruktionerna i den här artikeln gäller Excel 2019, 2016, 2013, 2010; och Excel för Microsoft 365.
Bottom Line
Uppslagsformeln kräver att COLUMN-funktionen är kapslad inuti VLOOKUP. Att kapsla en funktion innebär att den andra funktionen anges som ett av argumenten för den första funktionen.
Ange självstudiedata
I den här handledningen anges funktionen COLUMN som argument för kolumnindexnummer för VLOOKUP. Det sista steget i handledningen innebär att man kopierar uppslagsformeln till ytterligare kolumner för att hämta ytterligare värden för den valda delen.
Det första steget i den här handledningen är att ange data i ett Excel-kalkylblad. För att följa stegen i denna handledning, skriv in data som visas i bilden nedan i följande celler:
- Ange det översta dataintervallet i cellerna D1 till G1.
- Ange det andra området i cellerna D4 till G10.
Sökkriterierna och uppslagsformeln som skapats i denna handledning skrivs in på rad 2 i arbetsbladet.
Denna handledning inkluderar inte den grundläggande Excel-formateringen som visas i bilden, men detta påverkar inte hur uppslagsformeln fungerar.
Skapa ett namngivet intervall för datatabellen
Ett namngivet område är ett enkelt sätt att referera till ett dataintervall i en formel. I stället för att skriva cellreferenserna för data, skriv namnet på intervallet.
En andra fördel med att använda ett namngivet intervall är att cellreferenserna för detta intervall aldrig ändras även när formeln kopieras till andra celler i kalkylbladet. Områdesnamn är ett alternativ till att använda absoluta cellreferenser för att förhindra fel vid kopiering av formler.
Omfångsnamnet inkluderar inte rubrikerna eller fältnamnen för data (som visas på rad 4), bara data.
-
Highlight celler D5 till G10 i kalkylbladet.
Image -
Placera markören i namnrutan ovanför kolumn A, skriv Table och tryck sedan Enter. Cellerna D5 till G10 har intervallnamnet Tabell.
Image - Räckviddsnamnet för VLOOKUP-tabellmatrisargumentet används senare i denna handledning.
Öppna dialogrutan VLOOKUP
Även om det är möjligt att skriva uppslagsformeln direkt i en cell i ett kalkylblad, tycker många att det är svårt att hålla syntaxen rak - särskilt för en komplex formel som den som används i den här handledningen.
Som ett alternativ, använd dialogrutan VLOOKUP Function Arguments. Nästan alla Excel-funktioner har en dialogruta där var och en av funktionens argument skrivs in på en separat rad.
-
Välj cell E2 i kalkylbladet. Det här är platsen där resultaten av den tvådimensionella uppslagsformeln visas.
Image -
På menyfliksområdet, gå till Formulas-fliken och välj Lookup & Reference.
Image -
Välj VLOOKUP för att öppna dialogrutan Function Arguments.
Image - Dialogrutan Funktionsargument är där parametrarna för VLOOKUP-funktionen skrivs in.
Ange uppslagsvärdeargumentet
Norm alt matchar uppslagsvärdet ett datafält i den första kolumnen i datatabellen. I det här exemplet hänvisar uppslagsvärdet till namnet på den del som du vill hitta information om. De tillåtna typerna av data för uppslagsvärdet är textdata, logiska värden, siffror och cellreferenser.
Absoluta cellreferenser
När formler kopieras i Excel ändras cellreferenser för att återspegla den nya platsen. Om detta händer ändras D2, cellreferensen för uppslagsvärdet, och skapar fel i cellerna F2 och G2.
Absoluta cellreferenser ändras inte när formler kopieras.
För att förhindra fel, konvertera cellreferens D2 till en absolut cellreferens. För att skapa en absolut cellreferens, tryck på F4-tangenten. Detta lägger till dollartecken runt cellreferensen som $D$2.
-
I dialogrutan Funktionsargument, placera markören i textrutan lookup_value. Välj sedan cell D2 i kalkylbladet för att lägga till denna cellreferens till lookup_value. Cell D2 är där delens namn kommer att anges.
Image -
Utan att flytta insättningspunkten, tryck på F4 för att konvertera D2 till den absoluta cellreferensen $D$2.
Image - Lämna dialogrutan VLOOKUP-funktion öppen för nästa steg i handledningen.
Ange tabellen Array-argument
En tabellmatris är den datatabell som sökformeln söker för att hitta den information du vill ha. Tabellmatrisen måste innehålla minst två kolumner med data.
Den första kolumnen innehåller uppslagsvärdesargumentet (som ställdes upp i föregående avsnitt), medan den andra kolumnen söks av uppslagsformeln för att hitta den information du anger.
Argumentet för tabellmatrisen måste anges antingen som ett intervall som innehåller cellreferenserna för datatabellen eller som ett intervallnamn.
För att lägga till tabellen med data till funktionen VLOOKUP, placera markören i table_array textrutan i dialogrutan och skriv Tableför att ange intervallnamnet för detta argument.
Nest the COLUMN-funktionen
Vanligtvis returnerar VLOOKUP endast data från en kolumn i en datatabell. Denna kolumn ställs in av argumentet för kolumnindexnummer. I det här exemplet finns det dock tre kolumner, och kolumnindexnumret måste ändras utan att redigera uppslagsformeln. För att åstadkomma detta, kapslar du funktionen COLUMN i funktionen VLOOKUP som argumentet Col_index_num.
När funktioner kapslar, öppnar inte Excel den andra funktionens dialogruta för att ange dess argument. KOLUMN-funktionen måste anges manuellt. Funktionen COLUMN har bara ett argument, referensargumentet, som är en cellreferens.
Funktionen COLUMN returnerar numret på kolumnen som anges som referensargument. Den konverterar kolumnbokstaven till en siffra.
För att hitta priset på en vara, använd data i kolumn 2 i datatabellen. Det här exemplet använder kolumn B som referens för att infoga 2 i argumentet Col_index_num.
-
I dialogrutan Function Arguments, placera markören i textrutan Col_index_num och skriv COLUMN(. (Se till att inkludera den öppna runda konsolen.)
Image -
I kalkylbladet, välj cell B1 för att ange den cellreferensen som referensargument.
Image - Skriv en slutande runda parentes för att slutföra COLUMN-funktionen.
Ange VLOOKUP Range Lookup-argument
VLOOKUPs Range_lookup-argument är ett logiskt värde (TRUE eller FALSE) som anger om VLOOKUP ska hitta en exakt eller ungefärlig matchning med Lookup_value.
- TRUE eller utelämnad: VLOOKUP returnerar en nära matchning till Lookup_value. Om en exakt matchning inte hittas returnerar VLOOKUP det näst största värdet. Data i den första kolumnen i Table_array måste sorteras i stigande ordning.
- FALSE: VLOOKUP använder en exakt matchning med Lookup_value. Om det finns två eller fler värden i den första kolumnen i Table_array som matchar uppslagsvärdet, används det första värdet som hittas. Om en exakt matchning inte hittas returneras ett N/A-fel.
I den här handledningen kommer specifik information om ett visst hårdvaruobjekt att slås upp, så Range_lookup är inställt på FALSE.
I dialogrutan Funktionsargument, placera markören i textrutan Range_lookup och skriv False för att tala om för VLOOKUP att returnera en exakt matchning för data.
Välj OK för att slutföra uppslagsformeln och stänga dialogrutan. Cell E2 kommer att innehålla ett N/A-fel eftersom sökkriteriet inte har angetts i cell D2. Detta fel är tillfälligt. Det kommer att korrigeras när uppslagskriterierna läggs till i det sista steget i denna handledning.
Kopiera uppslagsformeln och ange kriterier
Uppslagsformeln hämtar data från flera kolumner i datatabellen samtidigt. För att göra detta måste uppslagsformeln finnas i alla fält som du vill ha information från.
För att hämta data från kolumnerna 2, 3 och 4 i datatabellen (priset, artikelnumret och leverantörens namn), ange ett delnamn som Lookup_value.
Eftersom data läggs ut i ett vanligt mönster i kalkylbladet kopierar du uppslagsformeln i cell E2 till celler F2 och G2 När formeln kopieras uppdaterar Excel den relativa cellreferensen i funktionen COLUMN (cell B1) för att återspegla formelns nya plats. Excel ändrar inte absolut cellreferens (som $D$2) och det namngivna intervallet (tabell) när formeln kopieras.
Det finns mer än ett sätt att kopiera data i Excel, men det enklaste sättet är att använda Fill Handle.
-
Välj cell E2, där uppslagsformeln finns, för att göra den till den aktiva cellen.
Image -
Dra påfyllningshandtaget över till cell G2. Cellerna F2 och G2 visar N/A-felet som finns i cell E2.
Image -
För att använda uppslagsformlerna för att hämta information från datatabellen, välj cell D2 i kalkylbladet, skriv Widget och tryck på Enter.
Image Följande information visas i cellerna E2 till G2.
- E2: $14,76 - priset på en widget
- F2: PN-98769 - artikelnumret för en widget
- G2: Widgets Inc. - namnet på leverantören för widgets
-
För att testa VLOOKUP-matrisformeln, skriv in namnet på andra delar i cell D2 och observera resultaten i cellerna E2 till G2.
Image - Varje cell som innehåller uppslagsformeln innehåller olika data om hårdvaruobjektet du sökte efter.
Funktionen VLOOKUP med kapslade funktioner som COLUMN ger en kraftfull metod för att slå upp data inuti en tabell med andra data som en uppslagsreferens.
