# EXCEL 2010 Check if sub string value in cell match with other string from range of cells

I am stuck with this one from hours.

I have range with cells with string values:

A1 text1

A2 text2

An text3

And other column with other string values like:

B1 text1sampletext

B2 text2sampletext

B3 text3sampletext

B4 text1sampletext

B5 text1sampletext

I have to check if text in column A is sub string of text in column B. If it is, to set in column C the text from column A.

Like this:

B1 text1sampletext - C1 text1

B2 text2sampletext - C1 text2

B3 text3sampletext - C1 text3

B4 text1sampletext - C1 text1

B5 text1sampletext - C1 text1

-

## migrated from stackoverflow.comSep 22 '12 at 12:03

This question came from our site for professional and enthusiast programmers.

This should work in Excel 2010 and doesn't require "array entry"

`=IFERROR(LOOKUP(2^15,FIND(A\$1:A\$3,B1),A\$1:A\$3),"not found")`

`FIND` is case-sensitive, if you don't want the formula to be case-sensitive replace `FIND` wiith `SEARCH`

Assumes that A1:A3 doesn't contain blanks

-
impressive :) (damn, we learn every day, I never use LOOKUP this way... but I should) – JMax Mar 1 '12 at 13:43
This is perfect. Thank you very much. I have lost trying to do this with if,iferror and search. – Joro Mar 1 '12 at 13:50
+1 Damn. I should have recalled this from a prior "wow thats a better method" experience. kudos – brettdj Mar 1 '12 at 21:58
That's some fine formulating! – Doug Glancy Mar 2 '12 at 4:17

I didn't manage for now to make Dave (brettdj)'s solution working.

Here is a UDF that would work:

``````Function copyText(rSubstr As Range, rText As Range)
'Check if any of the cell value from rSubstr exists in rText
Dim c As Range

If rText.Count > 1 Then Exit Function

For Each c In rSubstr
If InStr(1, rText.Value, c.Value) > 0 Then
copyText = c.Value
Exit Function
End If
Next c
End Function
``````

Simply use this function in your worksheet:

``````=copyText(\$A\$1:\$A\$3;B1)
``````
-

No doubt Barry will embarrass me

But something like this in C1 and copied down will work for xl07 and xl10

Press together to enter this as an array formula

`=IFERROR(INDEX(A\$1:A\$3,MATCH(TRUE,NOT(ISERROR(FIND(A\$1:A\$3,B1))),0)),"not found")`

for all xl versions again with Press `=IF(NOT(ISNA(MATCH(TRUE,NOT(ISERROR(FIND(A\$1:A\$3,B1))),0))),INDEX(A\$1:A\$3,MATCH(TRUE,NOT(ISERROR(FIND(A\$1:A\$3,B1))),0)),"not found")`

-
I was not able to use your code. The first give me errors, i try to fix it, but still no working. The second gives me only "not found". Could assist to find the problem? – Joro Mar 1 '12 at 10:44
the second argument is missing in the first formula in the `ISERROR` formula inside the `MATCH`. I am looking for an answer to make it work – JMax Mar 1 '12 at 13:32
the second formula worked OK for me but 1st formula needs a closing parenthesis to work, suggested edit to that effect – barry houdini Mar 1 '12 at 15:39
@barryhoudini thx, I see you have made the update – brettdj Mar 1 '12 at 21:57