VLOOKUP

[Guest]

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.

 

[Ragdyer]

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.

 

[Gord Dibben]

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