Lookup returning more than one column ?

[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 ?

 

[Don Guillett]

One way
=vlookup(a1,range,2,0)&" "&vlookup(a1,range,3,0)etc

 

[Domenic]

Hi Anthony,

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

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

Hope this helps!

 

[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 !

 

[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