 # 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

# Re: Need a formula to output specific information

``=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. # Re: Need a formula to output specific information

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

``=XLOOKUP(C14,C5:C7,FILTER(D5:F7,D4:F4=D13))`` # Re: Need a formula to output specific information

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

# Re: Need a formula to output specific information

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