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
Excel formula contains partial text - Best answers
Excel if cell contains partial text - Best answers
Partial match excel - Guide
Excel conditional formatting if another cell contains specific text - Excel Forum
Excel if color cell - Guide
Excel if cell contains text then copy to another sheet ✓ - Excel Forum
If cell contains date then return value ✓ - Office Software 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

If a cell contains any text then add a value

How to use conditional format based on text?

Excel - IF function with multiple words

help me to add particular cells

Counting cells containing only numbers

Similar discussions

Subscribe To Our Newsletter!