lookup returning several cells

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

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

[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

"Andre" wrote:

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

[Andre]

Worked great. Thank you.

"Toppers" wrote:

> 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
>
> "Andre" wrote:
>
> > 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.

[Andre]

Thank you for the help. Problem solved.

"VBA Noob" wrote:

>
> 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 Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561251
>
>