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

If cell contains partial text
If cell contains partial text then return value in another cell - Best answers
If cell contains any text - Best answers
If cell contains date then return value ✓ - Excel Forum
Excel "IF" function w/ date in test cell ✓ - Excel Forum
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
Excel formula to check if cell contains a date - Excel Forum
If cell A1 has text then cell B2 has today's Date ✓ - 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

Highlight cell if another cell contains specific text

If a cell contains any text then add a value

Counting cells containing only numbers

If cell = date return yes or no.

Excel - IF function with multiple words

Similar discussions

Subscribe To Our Newsletter!