XIRR with non-adjacent cells

[Specialist]

Hello guys,
I need to add to XIRR formula another cell/group of cells which are not adjacent to the initial range, for both values and dates.
How can I do that?
In Green the XIRR formula used and in Yellow what I'd need to be added to XIRR formula.
I needed to be non-adjacent cause I need to move what it is in yellow in another sheet.

Thank you everyone

 

[Peter Bartholomew]

With 365 you can use VSTACK to append data to arrays.

= LET(
    historicData,   FILTER(Transactions, Transactions[NAME]=name),
    combinedValues, VSTACK(CHOOSECOLS(historicData,3), amount),
    combinedDates,  VSTACK(CHOOSECOLS(historicData,2), date),
    XIRR(combinedValues,  combinedDates)
 )

I stopped using legacy Excel at the beginning of 2019 and firmly intend never to use it again, but it should be possible to extend your range references that are used to generate row numbers and test it within an IF statement to either return the corresponding array element or return the final values from your other range.

 

[p45cal]

Explore the formula in cell B1 of the attached. It's similar to Peter's.
Below it, is a short Lambda formula.
Note that for the XIRR function, the earliest date must have a negative value associated with it.

@Peter Bartholomew , I don't think you attached the correct file in your message?

 

[Peter Bartholomew]

@p45cal
Thanks for the info!
Hopefully this one is correct.

p.s. I love the Lambda function! So neat and clear.

 

[Specialist]

Thank you everyone, much appreciated!