Using the RESULT from one formula as a reference in a different formula

[brent74]

Ok, so I have a sheet of cash flows by customer and I need to determine the IRR for each customer. The XIRR formula calls for the following inputs:
XIRR(values, dates, guess).

The challenge is that the 'values' and 'dates' are not relative and are completely random per each customer. I cannot simply copy the formula and paste it down for every customer because the ranges are random and different for every customer.

What I did is create 2 new formulas (columns) that identify each cell/row range for the 'values' and 'dates' needed for the above XIRR formula. The results of these new columns give me the correct 'values' and 'dates'. However, if I simply plug in the working formulas into my IRR 'values' and IRR 'dates', the result is #value!.

Here are the formulas I created that result in the correct 'value' and 'dates':

Value Formula: =CONCATENATE(VLOOKUP(B42,'Disb Date Table'!$A$2:$E$54,3,0),ROW()+9,":",VLOOKUP($B42,'Disb Date Table'!$A$2:$E$54,5,0),ROW()+9) this returns 'J51:AX51'

Date Formula: CONCATENATE(VLOOKUP(B42,'Disb Date Table'!$A$2:$E$54,3,0),,ROW()-1,":",VLOOKUP($B42,'Disb Date Table'!$A$2:$E$54,5,0),ROW()-1)
this returns 'J41:AX41'

So what I'm trying to accomplish in my XIRR formula is the following: = XIRR(J51:AX51,J41:AX41,1%) << this applies to one specific customer, obviously a different customer will have different values and dates, hence the need for a solution

Hopefully this post is clear To summarize, I need to reference my 'value' and 'dates' formula within the XIRR formula. I know I've done this before with simpler formulas. There has to be a way, right?

TIA

[oeldere]

Maybe you have to wrap a indirect formula, I am not sure, but you could give it a try.

= XIRR(indirect(formula1),indirect(formula2),1%)

[brent74]

Maybe you have to wrap a indirect formula, I am not sure, but you could give it a try.

= XIRR(indirect(formula1),indirect(formula2),1%)

YES! This absolutely worked. Thanks!!

Final formula - =XIRR(INDIRECT(BA42),INDIRECT(BB42),1%)

[oeldere]

Thanks for the rep.

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.