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.

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.

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.