Hitta flera datafält med Excel VLOOKUP

Genom att kombinera ExcelVLOOKUP funktion medKOLUMN funktion kan vi skapa en uppslagsformel som låter dig returnera flera värden från en enda rad i en databas eller ett datatabell.

01 av 10

Returnera flera värden med Excel VLOOKUP

Genom att följa stegen nedan anges skapningsformeln som ses i bilden ovan, som returnerar flera värden från en enda datapost.

Lookupformeln kräver att KOLUMN funktion är kapslad inuti VLOOKUP. Att nollställa en funktion innebär att du anger den andra funktionen som en av argumenten för den första funktionen.

I denna handledning, KOLUMN funktionen kommer att anges som kolumnindex siffra argument för VLOOKUP. Det sista steget i handledningen innebär att du kopierar sökformuläret till ytterligare kolumner för att hämta ytterligare värden för den valda delen.

02 av 10

Ange handledningsdata

Det första steget i handledningen är att ange data i ett Excel-kalkylblad. För att följa stegen i handledningen, ange data som visas i bilden ovan i följande celler:

• Ange det högsta utbudet av data i celler Dl till G1.
• Ange det andra intervallet i celler D4 till G10.

Sökkriterierna och uppslagningsformeln som skapades under denna handledning kommer att skrivas in rad 2 av arbetsbladet.

Handledningen innehåller inte formateringen som ses i bilden, men det påverkar inte hur uppslagningsformeln fungerar. Information om formateringsalternativ som liknar dem som ses ovan finns i denna grundläggande Excel-formateringstutorial.

03 av 10

Skapa en namngiven räckvidd för datatabellen

Ett namngivna område är ett enkelt sätt att hänvisa till en rad data i en formel. I stället för att skriva in cellreferenser för data kan du bara skriva namnet på intervallet.

En andra fördel med att använda ett namngivna område är att cellreferenser för detta intervall aldrig förändras, även när formeln kopieras till andra celler i arbetsbladet. Områdesnamn är därför ett alternativ till att använda absoluta cellreferenser för att förhindra fel vid kopiering av formler.

Områdesnamnet innehåller inte rubrikerna eller fältnamnen för data (rad 4) men endast själva data.

1. Markera celler D5 till G10 i arbetsbladet för att välja dem.
2. Klicka på Namnlåda ligger ovanför kolumn A.
3. Typ Tabell in i Namnlåda.
4. tryck på Stiga på tangent på tangentbordet.
5. Celler D5 till G10 har nu intervallnamnet på Tabell. Vi kommer att använda namnet på VLOOKUP tabellmatris argument senare i handledningen.

04 av 10

Öppna dialogrutan VLOOKUP

Även om det är möjligt att bara skriva vår lookup-formel direkt i en cell i ett kalkylblad, är det svårt för många att hålla syntaxen rakt - speciellt för en komplex formel, som den vi använder i denna handledning.

Ett alternativ, i detta fall, är att använda VLOOKUP dialog ruta. Nästan alla Excel-funktioner har en dialogruta som låter dig ange varje funktions argument på en separat rad.

Handledning steg

1. Klicka på cell E2 av arbetsbladet - den plats där resultaten av den tvådimensionella uppslagningsformeln kommer att visas.
2. Klicka på Formuleringsfliken av band.
3. Klicka på Lookup & Reference alternativet i band för att öppna nedrullningsfunktionen.
4. Klicka på VLOOKUP i listan för att öppna funktionens dialogruta.

05 av 10

Ange argumentet Argument för sökning

Normalt är slå upp värde matchar ett fält av data i den första kolumnen i datatabellen. I vårt exempel är slå upp värde hänvisar till namnet på maskinvarudelen där vi vill hitta information. De tillåtna typerna av data för slå upp värde är textdata, logiska värden, tal och cellreferenser.

Absolut Cell Referenser

Normalt, när formler kopieras i Excel, ändras cellreferenser för att återspegla deras nya plats. Om detta händer, D2, cellreferensen för slå upp värde, kommer att ändras när formeln kopieras och skapar fel i celler F2 och G2.

Absoluta cellreferenser ändras inte när formler kopieras.

För att förhindra felen konverterar vi cellreferensen D2 till en absolut cellreferens. Absoluta cellreferenser skapas genom att trycka på F4 tangent på tangentbordet. Genom att göra så läggs dollartecken runt cellreferensen, såsom $ D $ 2.

1. Klicka på letauppvärde rad i dialogrutan.
2. Klicka på cell D2 att lägga till denna cellreferens till letauppvärde linje - det här är cellen där vi skriver in delnamnet om vilket vi söker information
3. Utan att flytta infogningspunkten, tryck på F4 tangent på tangentbordet för att konvertera D2 in i den absoluta cellreferensen $ D $ 2.
4. Lämna VLOOKUP Funktionsdialogrutan öppnas för nästa steg i handledningen.

06 av 10

Inmatning av argumentet för tabellarray

Tabellmatrisen är tabellen med data som sökformuläret söker efter att hitta den information vi vill ha.Tabellmatrisen måste innehålla minst två kolumner med data.

Den första kolumnen innehåller argumentet för uppslagningsvärde (föregående steg i handledningen), medan den andra kolumnen kommer att sökas med uppslagsformuläret för att hitta den information vi specificerar.

Argumentet för tabelluppsättning måste anges som antingen ett intervall som innehåller cellreferenser för datatabellen eller som en radnamn.

1. Klicka på Tabellmatris rad i dialogrutan.
2. Typ Tabell för att ange intervallnamnet för detta argument.
3. Lämna VLOOKUP Funktionsdialogrutan öppnas för nästa steg i handledningen.

07 av 10

Hämtar COLUMN-funktionen

I vanliga fall VLOOKUP returnerar bara data från en kolumn i en datatabell och denna kolumn ställs in av kolumnindexnummer argument.

I det här exemplet har vi emellertid tre kolumner som vi vill återställa data från så vi behöver ett sätt att enkelt ändra kolumnindexnummer utan att redigera vår lookup formel - det är här KOLUMN funktionen kommer in.

Genom att ange den som kolumnindexnummer argument, det kommer att ändras när uppslaget formel kopieras från cell D2 till celler E2 och F2 senare i handledningen.

Nesting Funktioner

De KOLUMN funktion fungerar därför som VLOOKUP's kolumnindexnummer argument; detta uppnås genom att nesta KOLUMN funktion inuti VLOOKUP i kolumnindex rad i dialogrutan.

Ange COLUMN-funktionen manuellt

När det gäller häckningsfunktioner tillåter inte Excel att öppna den andra funktionens dialogruta för att ange dess argument. De KOLUMN funktion måste därför anges manuellt i kolumnindex linje. De KOLUMN funktionen har bara ett argument - den Referens argument som är en cellreferens.

Välja COLUMN-funktionens referensargument

De KOLUMN funktionens jobb är att returnera numret på kolumnen som anges som Referens argument. Med andra ord omvandlar den kolumnbokstaven till ett nummer med kolumn A att vara den första kolumnen, kolumn B den andra och så vidare.

Sedan det första fältet är det priset på objektet, som finns i kolumn två i datatabellen. Vi kan välja cellreferensen för någon cell i kolumn B som den Referens Argument för att få numret 2 för kolumnindex argument.

1. I VLOOKUP funktion dialogrutan, klicka på kolumnindex linje.
2. Skriv funktionsnamnet kolumn följt av en öppen rund konsol.
3. Klicka på cell B1 i arbetsbladet för att ange den cellreferensen som Referens argument.
4. Skriv a stängningsrunda fästet att slutföra KOLUMN fungera
5. Lämna VLOOKUP Funktionsdialogrutan öppnas för nästa steg i handledningen.

08 av 10

Ange VLOOKUP Range Lookup Argument

VLOOKUP's ungefärlig argument är ett logiskt värde (SANN eller FALSK) som anger om du vill ha det VLOOKUP för att hitta en exakt eller en ungefärlig matchning till letauppvärde.

• Om SANN eller om detta argument utelämnas: VLOOKUP returnerar antingen en exakt matchning till letauppvärdeEller om en exakt match inte hittas, VLOOKUP returnerar nästa största värde. För formeln att göra detta, uppgifterna i den första kolumnen i Tabellmatris måste sorteras i stigande ordning.
• Om FALSK: VLOOKUP kommer bara att använda en exakt matchning till letauppvärde. Om det finns två eller flera värden i den första kolumnen i Tabellmatris som matchar uppslagningsvärdet, används det första värdet som hittats. Om en exakt match inte hittas, a # N / A fel returneras.

I denna handledning, eftersom vi letar efter specifik information om en viss maskinvara, kommer vi att ställa in ungefärlig lika med Falsk.

1. Klicka på ungefärlig rad i dialogrutan.
2. Skriv ordet Falsk i denna linje för att ange att vi vill ha VLOOKUP att returnera en exakt matchning för de data vi söker.
3. Klick ok för att slutföra formuläret för uppslag och stäng dialogrutan.
4. Eftersom vi ännu inte har angivit kriterierna för uppslagning cell D2 en # N / A-fel kommer att vara närvarande i cell E2.
5. Det här felet kommer att korrigeras när vi lägger till kriterierna för uppslag i det sista steget i handledningen.

09 av 10

Kopiera sökformuläret

Uppslagsformeln är avsedd att hämta data från flera kolumner i datatabellen samtidigt. För att göra detta måste uppslagningsformuläret finnas i alla fält som vi vill ha information om.

I den här handledningen vill vi att den ska hämta data från kolumner 2, 3, och 4 av datatabellen - det vill säga priset, delenummeret och leverantörens namn när vi anger ett partiellt namn som letauppvärde.

Eftersom uppgifterna läggs ut i ett regelbundet mönster i kalkylbladet kan vi kopiera uppslagsformeln i cell E2 till celler F2 och G2.

När formeln kopieras, uppdaterar Excel den relativa cellreferensen i KOLUMN funktion (cell B1) för att återspegla formelens nya plats.

Också, Excel ändrar inte absolut cellreferens $ D $ 2 och det angivna intervallet Tabell som formeln kopieras.

Det finns mer än ett sätt att kopiera data i Excel, men förmodligen är det enklaste sättet att använda Fyll handtag.

1. Klicka på cell E2, där lookupformeln finns, för att göra den till den aktiva cellen.
2. Placera muspekare över svart kvadrat i nedre högra hörnet - pekaren ändras till ett plustecken för att beteckna fyllningshandtaget.
3. Klicka på vänster musknapp och dra fyllhandtaget över till cell G2.
4. Släpp musknappen och cell F3 bör innehålla den tvådimensionella uppslagningsformeln.
5. Om det görs korrekt, celler F2 och G2 bör nu också innehålla # N / A fel som är närvarande i cell E2.

10 av 10

Ange sökningskriterierna

När genomsökningsformuläret har kopierats till erforderliga celler kan det användas för att hämta information från datatabellen. För att göra det skriver du in namnet på föremålet du vill hämta in i letauppvärde cell (D2) och tryck på Stiga på tangent på tangentbordet.

När en gång har gjorts, ska varje cell som innehåller uppslagningsformeln innehålla en annan bit data om hårdvaruposten du söker.

1. Klicka på cell D2 i arbetsbladet.
2. Typ widget in i cell D2 och tryck på Stiga på tangent på tangentbordet.
3. Följande information ska visas i celler E2 till G2.
   1. E2: $ 14,76 - priset på en widget
   2. F2: PN-98769 - delnumret för en widget
   3. G2: Widgets Inc. - leverantörens namn för widgets
4. Testa VLOOKUP array formel vidare genom att skriva in andra parts namn i cell D2 och observera resultaten i celler E2 till G2