Check for Partial Match in Absolute and Return Absolute

[excelnub]

Hi,

My title is probably not easily understood, but my attachment should clear it up.

I'm looking for a formula that can check if a specific cell contains the contents of any cell within a separate range. It should then return the value of the cell that it shows that match for.

For instance, assume A1 contains the text 7w63CARL JONES374 and my B column is blank. In C, I have 500 different strings, but one of them is CARL JONES. I would want B1 to scan all of C for that match, and if there's a match, it returns that value. Thus, B1 would show CARL JONES.

Please view the spreadsheet for a better explanation.

 

[eibi]

What about this array formula in C4:

=INDEX($D$4:$D$8,MATCH(1,(LEN(B4)<>LEN(SUBSTITUTE(B4,$D$4:$D$8,"")))+0,0))

See attached.

 

[Chihiro]

Another version.
=IFERROR(INDEX($D$4:$D$8,MAX(IF(ISERROR(FIND($D$4:$D$8,B4)),-1,1)*(ROW($D$4:$D$8)-3))),"NOT FOUND")

Confirmed as array (CSE)

In attached, $D$4:$D$8 is named range "lCanonical"

 

[eibi]

Hey @Chihiro,

I have heard that the IF(ISERROR()) combo adds processing time -- I don't actually know if it's true...If so, here's an array entered alternative using your method:

=IFERROR(INDEX($D$4:$D$8,MAX(IFERROR(FIND($D$4:$D$8,B4)^0,-1)*(ROW($D$4:$D$8)-3))),"NOT FOUND")

Does anybody know whether IFERROR or ISERROR are volatile -- or what the trade-off is in terms of processing time?

glance @Hui ??

 

[Chihiro]

@eibi
Good catch!

It's additional calculation step.
IFERROR(calc, "xxx") = IF(ISERROR(calc),"xxx",calc)
So IF(ISERROR()) needs to calculate twice as opposed to once.

Original portion:
=INDEX($D$4:$D$8,MAX(IF(ISERROR(FIND($D$4:$D$8,B4)),-1,1)*(ROW($D$4:$D$8)-3)))

Was written while back when I had to worry about 2003 compatibility.

 

[excelnub]

eibi,

Thank you so much! That worked perfectly. You've saved me lots of time. I'm done for the day, but tomorrow I am going to try and understand your code after some research.

Chihiro,

Thank you as well!

 

[bosco_yip]

Another option in non array formula, C4 copy down :

=IFERROR(LOOKUP(1,-FIND(D$4:D$8,B4),D$4:D$8),"Not Found")

Regards
Bosco

 

[eibi]

@excelnub:

@bosco_yip's is better -- for several reasons, but especially if you are going to have thousands of rows.

Array formulas are important, but they can really cripple your data processing time; particularly if you have a huge data set.

 

[Chihiro]

@bosco_yip
Very elegant. Never knew FIND inside LOOKUP can give result array, and ingenious use of "-" sign.

 

[NARAYANK991]

Hi ,

The issue with ISERROR is that the complex formula which is being evaluated , has its evaluation done twice , once as part of the condition test , and once as the final output.

In the case of ISERROR , the formula is evaluated once only when the valid results are over ; thus , if the number of valid results is 100 , and the formula itself has been copied down to 1000 rows , then the 100 cells which return valid results will have the formula being evaluated twice , while the 900 cells which do not return valid results will have the formula being evaluated once.

In the case of IFERROR , the problem is that even when the formula does not return a valid result , it is still being evaluated.

Narayan

 

[excelnub]

Another option in non array formula, C4 copy down :

=IFERROR(LOOKUP(1,-FIND(D$4:D$8,B4),D$4:D$8),"Not Found")

Regards
Bosco

Wow, I wish I would've come back before I ran the array through thousands of rows/columns, ha. Thanks a lot.

So IFERROR just decides what to display if whatever formula is used returns an error. Lookup and find I think I understand, and obviously the range/cell number I get.

What does that 1 and the hyphen in front of FIND do?

Also I replaced FIND with SEARCH since caps might differ in my data range. I don't think this should cause issues.

Oh, and while it's quicker, I think it only does exact match.

Thanks a lot, by the way.

 

[Nebu]

Hi:

Another variation

=IFERROR(LOOKUP(2^15,SEARCH(D$4:D$8,B4),D$4:D$8),"Not Found")

Thanks