Too many IF functions

G

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.
 
B

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top