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

Advertisements

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

Advertisements


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