IF statement w/ find text w/in string

[atown]

Hey,

wondering if anyone can solve this for me- havent been able to find a really good solution to this answer yet. I am trying to utilize an IF statement that will find a value within a text string and if the value exists, then return that value, else return another number.
A1:
23994 AD EDM1

using the above example, if A1 contains the value "EDM" then return "EDM" else return (another value)

 

[edzimmer]

Look at the post right above yours. I explained it in May

Sorry for the long time on my reply, I was having trouble getting pass my password login...
Thanks a lot. I'm still not sure I understand why the 255 "z" works with the CHOOSE {1,2} functions. I don't see examples of the CHOOSE function being used with the "{" and "}", I think there is some magic going on here.
Your formula is so great... Is there someway this could return a 2nd index value? For Example EDM match returns "Excel Development Match"? If so what would that formula be?

 

[edzimmer]

Sorry for the long time on my reply, I was having trouble getting pass my password login...
Thanks a lot. I'm still not sure I understand why the 255 "z" works with the CHOOSE {1,2} functions. I don't see examples of the CHOOSE function being used with the "{" and "}", I think there is some magic going on here.
Your formula is so great... Is there someway this could return a 2nd index value? For Example EDM match returns "Excel Development Match"? If so what would that formula be?

I guess to answer my own question, would be to have a vlookup formula around the main formula "LOOKUP(REPT("z",255),CHOOSE({1,2},"ANOTHER VALUE",LOOKUP(1000,FIND($E$1:$E$10,A1),$E$1:$E$10)))"referencing an index table. Still would like more deail on the internal workings of your formula?

 

[Scott Huish]

When you are looking up the last text lookup value in a table, looking up a value that is greater than anything that is likely to appear will return the last value from the array. Using Choose with values in curly braces will return the values as an array in this case an array of "Another Value", and the Text Value the lookup returns. It will ignore the #N/A if that is returned by the second lookup and returns the first value, otherwise it would return the last value in the array (the 2nd element created by CHOOSE)

 

[cfleming]

Thanks Scott! This helped me tremendously.
cf

 

[majdi_marmas]

Can we modify the code to display several results something which would work like this?
=IF(ISERROR(SEARCH(""EDM"",RC[-1])),""ANOTHER NUMBER"", ""EDM"")" And IF(ISERROR(SEARCH(""CCC"",RC[-1])),""ANOTHER NUMBER"", ""CCC is there"")" and IF(ISERROR(SEARCH(""LLL"",RC[-1])),""ANOTHER NUMBER"", ""LLL is included"")"
Or to mdify it using cell values to display,

Sub ifforall()
Range("A2").Select
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISERROR(SEARCH(""EDM"",RC[-1])),""ANOTHER NUMBER"", ""EDM"")"
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Loop
End SuB