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
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 26, 2010 at 03:17 PM
You need to use search and find functions
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?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > 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
Tom > rizvisa1 Posts 4478 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

Similar discussions

If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
How to convert number into text in Excel
boratechandu -
Raj -

9 responses
Excel - IF function with multiple words
Nick -
user -

14 responses