Help with Search Formula

[Segurde]

Hello,

I am looking to put together a formula that searches for a string of text and returns the following:

IF SEARCH “X Rewards” returns “X Rewards”
IF SEARCH “Gas Rewards” returns “Gas Rewards”
IF SEARCH “% OFF” returns “% Off”
IF SEARCH “0 Off” returns “Dollar Off”

[Ron Coderre]

With this list in A1:A5

There a X Rewards for you
You have gas rewards waiting
30% off is available
This item is 0 off
Bozo is a clown

and this lookup list in F1:G4

X Rewards      X Rewards
Gas Rewards    Gas Rewards
% OFF          % OFF
0 Off          Dollar Off

This regular formula, copied down, returns the referenced value for matching text

B1: =IFERROR(LOOKUP(10^99,SEARCH($F$1:$F$4,A1),$G$1:$G$4),"No Match")

For that data, these are the returned results:

X Rewards
Gas Rewards
% OFF
Dollar Off
No Match

Is that something you can work with?

[oeldere]

Reading your question, i think it would be.

=a1

but probably it is not that simple.

in that case please add an excel file, without confidential information, or better information.

[Richard Buttrey]

Hi,

Maybe

=IF(ISERROR(SEARCH("Rewards",A1)),IF(LEFT(A1,1)="0","Dollars Off",PROPER(A1)),A1)

[Pepe Le Mokko]

Looks like a cross post http://chandoo.org/forum/threads/hel...atement.17442/