Limits to If function (further to below)

**ugg** · 11-23-2007, 12:10 AM

Good afternoon,

I currently have the following IF formula (provided by Dave total hero!)

=IF(B73="DL1 ",E73/$C$12,IF(B73="DV9 ",E73/$C$13,IF(B73="EA9 ",E73/$C$5,IF(B73="DX8 ",E73/$C$2,IF(B73="DZ5 ",E73/$C$3,IF(B73="EA8 ",E73/$C$4,IF(B73="EB0 ",E73/$C$6,IF(B73="EBG ",E73/$C$7,""))))))))

The problem is, I dont appear to be able to add anymore IF's without getting an error.

Is there a limit to the number of IFs I can put in and is there a way around it?

**Ron Coderre** · 11-23-2007, 01:04 AM

Your version of Excel has a limit of 7 nested if functions.

Try this:

Put this list in G1:G8
G1: DX8
G2: DZ5
G3: EA8
G4: EA9
G5: EB0
G6: EBG
G7: DL1
G8: DV9

and this list in H1:H8
H1: =$C$2
H2: =$C$3
H3: =$C$4
H4: =$C$5
H5: =$C$6
H6: =$C$7
H7: =$C$12
H8: =$C$13

Then you can use this formula:
=IF(COUNTIF(G1:G8,B73),E73/VLOOKUP(B73,G1:H8,2,0),"")

The list in G1:H8 can be expanded WAY beyond 7 items.

Is that something you can work with?

**ugg** · 11-23-2007, 01:25 AM

Oh Ron,

I think it is going to work but I am too silly to get my head around it

I am sure you are on the right track, but dont quite get it.

It worked in your example but am having trouble applying it to my spreadsheet.

Its possible I have been looking at this too long.....Thankyou so much. I am sure it will work. I just have to get it right.

**ugg** · 11-23-2007, 01:50 AM

Oh Ron,

Thankyou. Of course it works. It was me just being silly. I had typed everything in to the example, but my actual data is a database export with spaces after the data so my x didnt match my y for the comparison. Fixed this all up and its fantastic.

Thankyou so much. WOO HOO!!!!!