Should be a easy How do I

  • Thread starter Thread starter Dan Hale
  • Start date Start date
D

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
 
=IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
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
 
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
 
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)
 
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?
 
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
 
Well actually you can have 8, 7 nested.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
=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)
 
Back
Top