How do I obtain the corresponding headers of the values that are responsible for a particular sum

[Pratik Panth]

How do I obtain the headers (in the row 1) that are responsible for the result obtained in the cell AV3.
Say for all the values that sum up to 780 (cell AV3) and less than 1000, I want the corresponding name of the headers in the row 1 (TEXT 20, TEXT 21, TEXT 23, TEXT 26, TEXT 28, TEXT 29, TEXT 32) in one cell or may be in the adjacent cells.
Kindly help.

 

[AliGW]

Your thread title is rubbish - tells us nothing at all about what you are looking for help with - far too generic!

Try this:

=INDEX($C$1:$AS$1,MATCH(AV3,$C3:$AS3,0))

 

[Pratik Panth]

Thank you AliGW. I edited it. I'm a noob, learning excel these days.
The formula that you gave me is not giving the desired output (i pasted the formula in cell AX3).

 

[AliGW]

OK, well unless I know what the desired output is, I can't help you.

 

[Pratik Panth]

TEXT 20, TEXT 21, TEXT 23, TEXT 26, TEXT 28, TEXT 29, TEXT 32 = desired output (for cell AV3).

I edited my post . I hope it helps.

 

[AliGW]

Ah, I see - sorry, that's above my pay grade.

 

[Pratik Panth]

Ok. Thanks AliGW!!

 

[bosco_yip]

Formula solution.

Since the formula used TEXTJOIN function to join the header results, it required Excel 2019 or Office 365 versions.

In "Result" AX2, array (CSE) formula copied down :

=TEXTJOIN(", ",,IF(A2:INDEX(A2:AS2,MATCH(AU2,INDEX(PROB(COLUMN(A$1:AS$1),A2:AS2/SUM(A2:AS2),,COLUMN(A$1:AS$1))*SUM(A2:AS2),)))>0,A$1:INDEX(A$1:AS$1,MATCH(AU2,INDEX(PROB(COLUMN(A$1:AS$1),A2:AS2/SUM(A2:AS2),,COLUMN(A$1:AS$1))*SUM(A2:AS2),))),""))

 

[Excel Wizard]

Bosco.
Wow, that is really awesome way to do the cumulative sum with PROB function.


=TEXTJOIN(", ",,REPT(C$1:AS$1,(MMULT(N(+C2:AS2),--(COLUMN(C2:AS2)>=TRANSPOSE(COLUMN(C2:AS2))))<AU2)*(C2:AS2>0)))