Need a formula to output specific information

Copper 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

@Humphriesre 

 

=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.

index match.JPG

 

@Humphriesre 

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

=XLOOKUP(C14,C5:C7,FILTER(D5:F7,D4:F4=D13))

Harun24HR_0-1659668941317.png

 

@Humphriesre 

 

With Items in A2:A4, Months in B1:D1 and respective search criterias in B6 & B7:

 

_Screenshot.png

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.