Embedded If formula not working

Microsoft Excel 2010
June 16, 2010 at 04:41:13
Specs: Windows XP

I'm trying to pull revenues from a sheet based on the month, where the revenues are currently displayed by t+1 etc. I'm using the below formula that seems to work for some of the rows but gives random inserts for others and does not move the cash flows into the next month should the month in the source sheet be updated.

Also - I have actually T+12 periods but excel only allows to insert 7 embedded If functions. Is there a better way to get the correct results?

BK4 = month
'Detailed Pipeline'!$EN$5 = where the cash flow is displayed

=IF($BK$4=HLOOKUP("T+1",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$EN$5),IF($BK$4=HLOOKUP("T+2",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$ER$5),IF($BK$4=HLOOKUP("T+3",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$EV$5),IF($BK$4=HLOOKUP("T+4",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$EZ$5),IF($BK$4=HLOOKUP("T+5",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$FD$5),IF($BK$4=HLOOKUP("T+6",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$FH$5),IF($BK$4=HLOOKUP("T+7",'Detailed Pipeline'!$EM$4:$GH$10000,5,FALSE),CHOOSE(1,'Detailed Pipeline'!$FL$5),0)))))))

Many thanks,


See More: Embedded If formula not working

Report •

June 16, 2010 at 10:01:47

Without knowing what data is in the worksheet "Detailed Pipeline" it is hard to give detailed advice.

There are a couple of points:
1. You don't need CHOOSE()
As you are always using CHOOSE(1, ... i.e., choosing the first item in a single item list, just use the cell address.
CHOOSE(1,'Detailed Pipeline'!$EN$5) becomes: 'Detailed Pipeline'!$EN$5

2. You are looking for a month number 1 to 12, but you are effectively converting the month number into a text string:
1 to T+1, 2 to T+2
You could just search for T+1 with HLOOKUP()
=HLOOKUP("T+" & Text(BK4,"#0"),...
That way you have a single HLOOKUP and you shouldn't need to nest them with lots of IF() statements.


Report •
Related Solutions

Ask Question