Nested XLOOKUP

New Contributor

I have a table with cash flow generated from stocks and bonds. Each row has year and month, a ticker, and amount. I want to summarize the data in a new table that has rows with cash flow organized by month. I tried to use the following nested XLOOKUP to capture the ticker, the year, and the month and return the cash flow. I get a #VALUE! error on all the cells. Here is the XLOOKUP i used:

 

=XLOOKUP([@[Ticker/Cusip]],CF[Ticker/
Cusip],XLOOKUP(Holdings!AP$3,CF[Year],XLOOKUP(Holdings!AP$4,CF[Month],CF[Total
CF - CAD],0,0,1)))

 

1 Reply
Hi, I don't think XLOOKUP is the right tool for the job here. It looks like SUMIFS may be the one, something like this:
=SUMIFS(CF[Total CF - CAD],CF[Ticker/ Cusip],[@[Ticker/Cusip]],CF[Year],Holdings!AP$3,CF[Month],Holdings!AP$4)