| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup returning several cells
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup returning several cells
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup returning several cells
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup returning several cells
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup returning several cells
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return a range of cells from a vertical lookup | Excel Worksheet Functions | |||
| lookup and return range of cells | Excel Worksheet Functions | |||
| Lookup formula - treat no-registered cells as blank | Excel Worksheet Functions | |||
| Skipping Blank Or Null Cells In a Lookup Function | Excel Worksheet Functions | |||
| Adding cells with alphabet and returning a numbric value | Excel Worksheet Functions | |||
Linear Mode
