HELP! IF function..... I think!

D

DCM84

Hi,

Im a beginner with excel and think I need to use the IF function to d
it.

OK, heres what i want to do:

At present, I am using this formula in cell A5:
=IF((A1="ACC"),"IIBD ACC", "INVALID CAUSE CODE")

This works fine and when ACC is entered into cell A1, IIDB ACC appear
in cell A5. But, I want to add more values to it, i.e, I want cell A
to still display IIDB ACC when ACC is entered OR another specifie
value such as ACC1. There are about 156 different values that i need.

Another problem is that for some of the values, displaying IIDB AC
would be incorrect as this is not the correct code for some of them.

I thought the obvious solution would be:
=IF((A1="ACC","ACC1","ACC2"....etc),"IIDB ACC", "INVALID CAUSE CODE")
for the values that IIDB ACC corresponds with, and a second formula:
=IF((A1="99","99a","100"....etc),"IIDB PD","INVALID PD")
for the values that it does not correspond with.

I have tried this and found it doesnt work (Im only a novice! hehe) an
also have found that i cannot combine the two statments to wor
together.

Can anyone enlighten me? Also, if you know how to do this, can you giv
me a little background info so that i can understand how it work
exactly?

I would be very very grateful!!
So thanx!!!!!

DCM8
 
E

Earl Kiosterud

DCM84,

I've had to make some assumptions about what you want. Make a list of the
acceptable codes (I used H1:H156). Put this in A5:

=IF(NOT(ISERROR(MATCH(A1, H1:H156, 0))), "IIBD " & A1, "invalid cause code")
 
D

DCM84

excellent - worked brill!

Jus one more thing, I have a list of the codes already in anothe
worksheet in the workbook that is used for a drop down menu. The item
in it are a named range so when using the formula you gave me, can
substitut H1:H156 with the name of the range (ValidCodes)?

Thanx loads,
DCM8
 
E

Earl Kiosterud

DCM84,

I hope by now you've gone ahead and tried it. It should look something
like:

=IF(NOT(ISERROR(MATCH(A1, ValidCodes, 0))), "IIBD " & A1, "invalid cause
code")
 
Top