Search Text in Two Columns and Return a Value

[jalenoes]

Hello all,


I'm using Excel 2007.


Data set:

--Column B----Column C----Column D----Column E----

1-LAMP Q----*value*----This is a fish----50,000----

2-*blank*----*value*----(K) This is a fish 2----250----

3-etc


J1=40000, J2=50000, K1=(K), L1=Q


Formulas:

Column B:

=IF(E1>=$J$2,"LAMP Q",IF(E1>=$J$1,"ON Q","")), copied downwards for all cells


What I want to do:

In Column C1:-

Evaluate D1 for text containing text in K1, if true = return value in K1,

If False, then evaluate B1 for text in L1, if true = return value in L1,

If False, return blank.


I tried combining the IF() and SEARCH() functions but it doesn't work. Maybe I'm not nesting it right. Is there a simpler and more elegant solution? I wish to keep this workbook free from VBA and Conditional Formatting and also backwards compatible with Excel 2003 (if possible).


Column C:

=IFERROR(IF((SEARCH($K$1,D1)>0),$K$1,(IF(SEARCH($L$1,B1)>0,$L$1,""))),"")

, copied downwards.


Help appreciated!

 

[NARAYANK991]

Hi ,


Let me know if I have understood your question :


1. Column B , through formulae , will consist of a) blanks b) ON Q c) LAMP Q


2. In column C , you want the following to happen : If the text in K is present in D , then C will contain the text in K ; if not , then if the text in L is present in B , then C will contain the text in L , otherwise C will be blank.


3. What will K and L contain ? Will it be the same text in all cells in columns K and L or will it be different in different cells ?


Narayan

 

[jalenoes]

Hi Narayan!


1. Yes, just as you described it.


2. Ditto.


3. Columns K and L will not contain anything. I only used Cells K1 and L1 to contain my search string.


Appreciate your reply! =)

 

[NARAYANK991]

Hi ,


Try the following formula :


=IF(IFERROR(FIND($K$1,D2),"")="",IF(IFERROR(FIND($L$1,B2),"")="","",$L$1),$K$1)


in cell C2. Copy down to the remaining relevant cells in column C.


Narayan

 

[jalenoes]

Wow, works like a charm!


Could I trouble you for a brief explanation on the breakdown of the formula?


Edit:

After some additional searching and trials, I found an alternative that works as well

=IF(ISNUMBER(SEARCH($K$1,D2)),$K$1,IF(ISNUMBER(SEARCH($L$1,B2)),$L$1,""))

 

[NARAYANK991]

Hi ,


Thanks. Basically , in Excel 2007 , the IFERROR function replaces the earlier combination of IF and ISERROR.


Thus the formula


=IFERROR(formula_1,replace_invalid_result)


is the same as


=IF(ISERROR(formula_1),replace_invalid_result,formula_1)


The rest of the formula is basically what you had already used.


Narayan

 

[jalenoes]

Ah, beginning to get the picture now.


I'll have to search some more to find out how exactly does the FIND() and SEARCH() functions differ and why do they both work in this case.


Thanks so much Narayan =)