Return all figures in a Range
 

G’Day
I am looking for a way to create a “Look up” that will return more then one figure.
I have a table that has 400 rows and 50 columns and I would like my look up to return the entire line of 50 figures.

I currently use 50 individual Vlookup’s

The Vlookup parameters change according to what the user wants to see via concatenated cells.


Thanking you,

Michael

Return all figures in a Range
 

"michaelp" wrote in message
...

G’Day
I am looking for a way to create a “Look up” that will return more then
one figure.
I have a table that has 400 rows and 50 columns and I would like my
look up to return the entire line of 50 figures.

I currently use 50 individual Vlookup’s

The Vlookup parameters change according to what the user wants to see
via concatenated cells.


It's possible.
You probable now use formulas that look like =VLOOKUP(A1,A100:A500,2) ,
=VLOOKUP(A1,A100:A500,3) etc.
Instead of writing formulas, each containing one column no., you could write
the column numbers as an array.
The formula would look like =VLOOKUP(A1,A100:A500,{2,3,4,5,6 ...49,50}).
In order to make this formula work, you have to input is as an array
formula:
1) Select the range that should contain the 49 figures (above example
assumes that the user provides the first value in A1, so select B1:AX1)
2) Input the formula
3) Instead of ENTER, use CTRL+SHIFT+ENTER. This will put curly braces aroung
the formula, indicating an array formula.


Cheers

Joerg

Thank you very much for your reply