# 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

• 32.2 KB Views: 7
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).

#### AliGW

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

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

#### AliGW

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

#### Pratik Panth

##### New Member
Ok. Thanks AliGW!!

#### 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),))),""))

#### Attachments

• 37.6 KB Views: 6
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

• 37.4 KB Views: 7