Should be a easy How do I

[Dan Hale]

I have a spreadsheet that I put Store names and addresses in the same cell
(say E5), I need a formula so that if E5 contains "Target" or "CVS" that in
cell F6 it will return a number. Should be easy can someone please show me
the formula?

Thanks

 

[Bob Phillips]

=IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dan Hale]

Thanks
Now let's add something else
Here is the formula real
=IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"")
Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return 60
like above, but if it contains "TARGET" return 120 else blank

Thanks

 

[Rookie 1st class]

In F6 paste
=if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can gang
up to 6 if statements together.
HTH Lou

 

[Michael M]

Hi
You can actually use 7 IF statements not 6.........but any more than that
would suggest you use a VLOOKUP table

HTH
Michael M

 

[Peo Sjoblom]

Actually you can trick Excel by dumbing down the formula

=IF(E6="A",60,""&IF(E6="B",60,"")&and so on

only limitation is the length of the formula, having said that I wouldn't
recommend it since the formula would be very ugly

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)

 

[Dan Hale]

Ok that's great to know, but the problem is that "WALGREENS" or "CVS" or
"Target" are not the only text in the cell so I need a formula that would
work like yours but checks if the cell contains those words?

 

[Michael M]

I still think you need a VLOOKUP table

Go to somewhere where you have some space, say G1.
In column G list all the stores
In column H list their values.

Go back to where you want the formula and use:
=VLOOKUP(E6,G1:H100,2, FALSE)

HTH
Michael M

 

[Bob Phillips]

Well actually you can have 8, 7 nested.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

=IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",E5))),60,
IF(ISNUMBER(FIND("TARGET",E5)),120,""))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)