Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Sep 22 '12 at 12:03

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

up vote 6 down vote accepted

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

share|improve this answer
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:

share|improve this answer

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")

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .