IF formula

M

Mandy

I am trying to create a IF formula to return certain information:

If B2=COMERCIAL FINANCE

I want it to return certain numbers like

if b2=comercial finance then return 24567
if comercial finance is not selected I want it to be blank

There will be a list to choose from like
COMERCIAL FINANCE=2345
CONSUMER & INDUSTRIAL=3455
CONSUMER HOME SERVICES=4579

And so forth
 
F

FSt1

hi
the "and so forth" part may complicate things.
in 2003, you are only allowed 7 levels in an if formula. 2007 has more....16
comes to mind but i'm quoting from research memory since i don't have 2007
yet.
but to build an if statement with your sample data...
=IF(B2="COMERCIAL FINANCE", 2456,IF(B2="CONSUMER & INDUSTRIAL", 3455,
IF(B2="CONSUMER HOME SERVICES", 4579,"")))

if you have a lot of catagories, you might what to look it to a lookup
formula.

regards
FSt1
 
C

Chad Portman

I would make a list of the possible names you need to lookup and then in the
column next to it make a list of the corresponding numbers. Then do a
VLOOKUP. So for example:

We will use Sheet 2 for the list of info.
On Sheet 2 column A type the list of names
On Sheet 2 column B type the list of numbers
Your example only list 3 things but lets say that ten things are in the list

Then the data you are entering is in Sheet 1 column A
Then in Sheet 1 cell B1 write this formula: =VLOOKUP(A1,Sheet2!$A$1:$B$10,2)

You will be able to drag the formula down and it will work.

What it does is check the cell against Sheet 2 column A and returns the
value in Sheet 2 column B.

Chad Portman
 
Top