lookup returning several cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

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