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

Vlookup if cell contains partial text
Match partial text string in one sheet to another text string in another sheet ✓ - Excel Forum
If cell contains date then return value ✓ - Office Software Forum
Excel: If Date =, then enter a value ✓ - Excel Forum
Excel "IF" function w/ date in test cell ✓ - Excel Forum
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum

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

How to use conditional format based on text?

Counting cells containing only numbers

Highlight cell if another cell contains specific text

Excel - IF function with multiple words

If a cell contains any text then add a value

Similar discussions

Subscribe To Our Newsletter!