lookup returning several cells

A

Andre

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.
 
V

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
 
T

Toppers

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

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

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

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

HTH
 
A

Andre

Worked great. Thank you.

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

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

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

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

HTH
Click to expand...
 
Top