Too many IF functions

[GHall]

I know there is a limit on how many nested IF functions a formula can have,
but is there a "work-around" for this? Here is my example:

=IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave
tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker
FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave
tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker
FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker
FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker
FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,))))))))

But I need to add in the last 3 months. Looking to compare todays date
(D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal
the amount of vacation time(tracker FY10'!C5) someone has. The above mess
works for the first 9 months, but to add in the last 3 months it fails,
saying I have too many nested IF's.
Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel
2007.

 

[barry houdini]

Hello G Hall

try this formula

=INDEX('Leave tracker FY10'!C5:J5,MATCH(TRUE,INDEX(D2<='Leave tracke
FY10'!C4:J4,0),0))

you can extend the ranges as require

 

[T. Valko]

Based on the logic of your formula something like this should work...

Array entered** :

=INDEX('Leave tracker FY10'!C5:N5,MATCH(TRUE,D33<='Leave tracker
FY10'!C4:N4,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[GHall]

Thanks Barry and T. Between the two of you, and a little tweaking, it works
great!

 

[T. Valko]

You're welcome. Thanks for the feedback!