Lookup returning more than one column ?

A

Anthony Slater

Please consider the following: -

A1 in Sheet 1 contains the value 123
A1 in sheet 2 contains 123.
B1 in sheet 2 contains "hello"
C1 in sheet 2 contains "goodbye"
D1 in sheet 2 contains "Beatles"

How can I look up value A1 to return B1, C1 and D1 all
together ?
 
D

Domenic

Hi Anthony,

Assuming that the range for your table is A1:D10 in Sheet 2 and your
lookup value is A1 in Sheet 1, try the following:

select the range B1:D1
type =VLOOKUP(A1,Sheet2!$A$1:$D$10,{2,3,4},0)
enter using CTRL+SHIFT+ENTER

Hope this helps!
 
A

Anthony

Thanks

I thought of that but the formula gets quite long.

Could I use CONCATENATE within the formula? or part of an
IF statement?

I have a 'verbal' idea on how I want it to work, but can't
seem to put within a formula !
 
D

Don Guillett

You could do it with a macro or a user defined function (macro made into a
custom formula) using offset.
or
=INDEX(J1:K10,MATCH(L1,H:H),1)&" "& INDEX(J1:K10,MATCH(L1,H:H),2)&" etc
 
Top