Previous Topic Next Topic

Lookup partial text contained in cell range

Closed
Tom - Feb 26, 2010 at 03:12 PM
 Tom - Feb 26, 2010 at 03:52 PM
Hello,
I am trying to write a vlookup formula that will determine whether a cell in a range contains the partial text in a cell.

Example:
A1: 123
A2: 456
A3: 389

B1: 13456
B2: 145
B3: 12384N
B4: 74E123

C1: "yes"
C2: "yes"
C3: "no"

need vlookup formula that will look for the contents of A1 (123) anywhere in a range of cells and return with "Yes" or something similar if found.

Thanks, in advance, for any help

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Feb 26, 2010 at 03:17 PM
You need to use search and find functions
0
Tom
Feb 26, 2010 at 03:22 PM
OK, but don't know what that formula should look like. If A1 contains the partial text, and the range is B1:B50, how do you ask the formula to look in a range of cells for the 123 contained in A1?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > Tom
Feb 26, 2010 at 03:33 PM
See if this helps you

https://ccm.net/forum/affich-46323-find-word-within-cell-put-value-in-adjacent#22
0
Tom > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 26, 2010 at 03:52 PM
No, not really (and you're right, it was turning into its own forum). I use vlookup quite often, but my formulas are generally pretty basic and straight-forward. What I don't know how to do is modify the formula so that it will identify the partial text or number and return a "yes."

Example: =vlookup(A1, B1:D50, 3, false) would look for an exact match for A1 and return the data from the 3 column. But if A1 doesn't have an exact match, but is contained partially in the contents of a cell or cells in a range, I need it to return "yes." Changing the "true" doesn't work b/c I have too many cells that contain similar data.

Thanks
0