Messy Formula needs clean up help!

[Danny Boy]

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add 1
more condition, but realized that I cannot add yet an 8th condition without
receiving an error message (I believe the limit is 7). Any suggestions to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00",""))))))))

 

[Teethless mama]

Setup a lookup table and use VLOOKUP function

 

[Danny Boy]

I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

 

[Fred Smith]

Lookup tables are a lot easier than the If statement you created. Don't be
so hard on yourself. Millions of people have used Vlookup successfully. You
can too.

You should also learn the difference between text and numbers. Using
"$150.00" makes this text, which can't be used in other formulas. You should
use 150.00, and format it for currency.

Regards,
Fred

 

[T. Valko]

attempting to setup a lookup table and use

VLOOKUP function would be difficult for me.

No it wouldn't. It's a lot easier than you think.

Enter these strings in the range AA1:AA7 -

Administrative Discharge Fee
Medication Non Compliance Fee
Taxi Fees
Phase Program No Show Fee
Record(s) Not Provided Fee
BC Not Provided Fee
File Re-Opening Fee

Enter the corresponding numeric values in the range AB1:AB7 -

150
50
10
150
100
50
150

Now you have a lookup table!

=VLOOKUP(A5,AA1:AB7,2,0)

You don't even need to use a "lookup" function in this case:

=SUMIF(AA1:AA7,A5,AB1:AB7)

 

[Danny Boy]

Wow, easier than I thought. Thanks Biff and everyone else.

TGIF!

Dan

 

[Danny Boy]

Hi Biff!

I tried your suggestion, and I'm still a bit confused (sorry)! My first
question is about your formula.

=VLOOKUP(A5,AA1:AB7,2,0)

1-What does cell A5 reference? Given that the strings and corresponding
numbers are in AA1-AA7 and AB1-AB7.

2-In what cell do I put the VLOOKUP formula so that it runs?

What I'm trying to (as you may have guessed), is to use a drop down menu
with the various charge types (Administrative Discharge Fee, Medication Non
Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
themselves appear in the corresponding columns. If the drop down menu is
blank, than no charges would appear.

Again, Thank You

 

[Gord Dibben]

VLOOKUP

=VLOOKUP(lookup item,lookup table,column index number,true or false)

A5 would be the cell with the chosen item


Gord Dibben MS Excel MVP