Return all figures in a Range

M

michaelp

G’Day
I am looking for a way to create a “Look up” that will return more the
one figure.
I have a table that has 400 rows and 50 columns and I would like m
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 se
via concatenated cells.


Thanking you,

Michae
 
J

Joerg

michaelp said:
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.
Click to expand...


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
 
M

michaelp

Thank you very much for your reply



"michaelp" [email protected] 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
Click to expand...
 
Top