lookup returning several cells

[Guest]

I'm trying to pull several cells from one worksheet based on a value from
another worksheet.

worksheet1
apples
oranges
pears

worksheet2
apples green red ripe
oranges naval Florida round
pears rotten ready sold

Based on worksheet1 apples should return from worksheet2, green, red, and
ripe. Any help is appreciated.

 

[VBA Noob]

Hi,

Try this array

=INDEX(Sheet2!$A$1:$A$3,MATCH(TRUE,ISNUMBER(SEARCH("*"&$A$1:$A$3&"*",A1)),0))

Use Ctrl + Shift + enter to use

VBA Noo

 

[Guest]

Assuming data i ws2 is in colums A to D AND A1 is value from WS1 e.g "Apple"

then = VLOOKUP(A1,ws2!A,2,false) will return "Green"

= VLOOKUP(A1,ws2!A,3,false) will return "Red"

= VLOOKUP(A1,ws2!A,4,false) will return "Ripe"

HTH

 

[Guest]

Worked great. Thank you.

Assuming data i ws2 is in colums A to D AND A1 is value from WS1 e.g "Apple"

then = VLOOKUP(A1,ws2!A,2,false) will return "Green"

= VLOOKUP(A1,ws2!A,3,false) will return "Red"

= VLOOKUP(A1,ws2!A,4,false) will return "Ripe"

HTH

 

[Guest]

Thank you for the help. Problem solved.