Forum Discussion
Need a formula to output specific information
I have a set of data that is sorted horizontally by month and vertically by item. I am trying to create a formula that will look for a specific month and then output data from a specific cell based on a specific item on the vertical axis. Is this possible to do?
4 Replies
With Items in A2:A4, Months in B1:D1 and respective search criterias in B6 & B7:
in B8:
=SUMPRODUCT( (B1:D1=B7) * (A2:A4=B6) * B2:D4 )or (Excel 2021 or 365)
=FILTER( XLOOKUP(B7,B1:D1,B2:D4), A2:A4=B6)or (Excel 2021 or 365)
=XLOOKUP(B6, A2:A4, XLOOKUP(B7,B1:D1,B2:D4))You can use XLOOKUP() and FILTER() to gain desired result. See the attached file.
=XLOOKUP(C14,C5:C7,FILTER(D5:F7,D4:F4=D13))=INDEX($C$4:$I$15,MATCH($C$19,$B$4:$B$15,0),MATCH($C$18,$C$2:$I$2,0))You can try this formula for the data layout of the example.
- This worked well for me for what I needed. Thank you.
