Forum Discussion
nested xlookup
Hello
My input data is
my desired output is
how to achieve this result, basically, i need to get the values for whatever date i enter in the column header. it needs to pull the values corresponding to input table.
i tried using nested xlookup. please guide me.
PS: i need to use in webexcel (excel 365)
Thanks
Kalyan,
See the attached version.
9 Replies
I would just insert a pivot based on that table change rows and column (transpose) to get the sums
why not put a pivot around the table and unpivot it to get your desired view?
KalyanPrasad Alternatively, you can try SUMIFS with XLOOKUP here as well:
=SUMIFS(XLOOKUP([@Report], Table1[#Headers], Table1), Table1[Date], B$1)SUMIFS criteria is not type specific, so a text string that looks like a valid date will be interpreted as a date. Please see the attached workbook if needed, which also contains one possible dynamic array solution for MS365...
- HansVogelaar Tejas_shah
having understood this, now i m looking for a combination of xlookup and sum within date range.
i have posted the query here. you may please check this and guide me.
https://techcommunity.microsoft.com/t5/excel/xlookup-and-sum-within-date-range/m-p/4214761 - KalyanPrasad
Report table convert to normal range than it will work- this works perfectly too.
i understood that the bottleneck lied in date values rendered functionless in that table format.
thank you.
See the attached version.
thank you. Xlookup also worked now. they trick lies in applying DATEVALUE. i saw that in your formula.
=XLOOKUP(DATEVALUE(Table25[[#Headers],[06-Aug-24]]),Table1[Date],XLOOKUP([@Report],Table1[[#Headers],[opening balance]:[balance]],Table1[[opening balance]:[balance]],"",0,1))
i entered this formula in B2 of report table. worked like magic.
thank you.The headers of a table are always text, even if they look like dates. That's why you need DATEVALUE.
