Need a formula to output specific information

New Contributor


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





You can try this formula for the data layout of the example.

index match.JPG



You can use XLOOKUP() and FILTER() to gain desired result. See the attached file.






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))



This worked well for me for what I needed. Thank you.