• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Pratik Panth

New Member
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.
 

Attachments

Last edited:

AliGW

Active Member
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

New Member
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).
 

Pratik Panth

New Member
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.
 

bosco_yip

Excel Ninja
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),))),""))

75289
 

Attachments

Last edited:

Excel Wizard

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

Attachments

Top