Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP
With VLOOKUP I can look in the first column of an array and moves across the
row to return the value of a cell. But what I need is a function that moves across the row and returns all the values that coincide with the first row. For example a recipe has different ingredients, what I need is a way to type in the product code and that excel deliver all the ingredients common to the code. |
#2
|
|||
|
|||
What you can do is copy the Vlookup formula across the columns, and have the
column index of the formula increment , so that each ingredient belonging to the recipe will be returned into individual cells. The column index in the formula can be constructed to *automatically* increment as you copy it across the columns by using a column reference instead of a plain index number. For example, =VLOOKUP($A$1,$D$10:$M$100,2,0) Can be revised to, =VLOOKUP($A$1,$D$10:$M$100,COLUMN(B1),0) Note ... even though you're looking to return the contents of Column E in the first formula, you'll use Column(B1) as the index because Column(B1) returns a "2", and as you copy across, will increment to "C1", "D1", ... etc. which will *automatically* increment the column index number to have the formulas return the entire contents of the looked up record. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "robert" wrote in message ... With VLOOKUP I can look in the first column of an array and moves across the row to return the value of a cell. But what I need is a function that moves across the row and returns all the values that coincide with the first row. For example a recipe has different ingredients, what I need is a way to type in the product code and that excel deliver all the ingredients common to the code. |
#3
|
|||
|
|||
Robert
One more way to enter the formulas. =VLOOKUP(cellref,table,{2,3,4,5,6},0) Select 5 cells across the row, say B1:F1 Enter the above in B1 which is active cell then hit CTRL + SHIFT + ENTER to increment the index number across the 5 cells. Gord Dibben Excel MVP On Sun, 20 Mar 2005 05:55:03 -0800, robert wrote: With VLOOKUP I can look in the first column of an array and moves across the row to return the value of a cell. But what I need is a function that moves across the row and returns all the values that coincide with the first row. For example a recipe has different ingredients, what I need is a way to type in the product code and that excel deliver all the ingredients common to the code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Vlookup Syntax Error | New Users to Excel | |||
VLOOKUP not working | Excel Worksheet Functions |