Excel 2007 : How to combine vlookup and search partial text

**belebala** · 03-24-2011, 12:48 PM

Hello All,

I want to create a formular to return a value by searching a partial text. For example, in Sheet 2 col B, I want to search Sheet 2 col A (ID001) from the partial text of Sheet 1 Col A (ID001 - Name 1) and return the value of Sheet 1 Col B to Sheet 2 Col B (Yes).

Sheet 1:
Col A Col B
ID001 - Name 1 Yes
ID002 - Name 2 No
ID003 - Name 3 No

Sheet 2:
Col A Col B
ID001 [Return Value]
ID003 [Return Value]

Thanks for your help in advance.

**NBVC** · 03-24-2011, 12:51 PM

Try

=Vlookup(A2&"*",'Sheet1'!A:B,2,FALSE)

**belebala** · 03-24-2011, 02:16 PM

Great, I forgot the wildcard. Thank you.

**garriebartolome** · 04-14-2011, 03:51 AM

On a similar case, I am trying to create a formula for the following scenario but to no avail. Please note that Cell in Col A Sheet1 have multiple values. e.g A1 contains "ID001, ID002, ID003"

Sheet 1:
Col A Col B
ID001, ID002, ID003 Yes
ID004, ID005, ID006 No

Sheet 2:
Col A Col B
ID002 [Return Value = Yes]
ID006 [Return Value = No]

**NBVC** · 04-14-2011, 06:56 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.