Aug 04 2022 10:23 AM
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?
Aug 04 2022 10:33 AM
=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.
Aug 04 2022 08:09 PM
You can use XLOOKUP() and FILTER() to gain desired result. See the attached file.
=XLOOKUP(C14,C5:C7,FILTER(D5:F7,D4:F4=D13))
Aug 05 2022 05:01 AM - edited Aug 05 2022 05:50 AM
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))
Aug 05 2022 08:03 AM