Index Match Return Column # based on Sum of Columns

[mkrecknagel]

Hello All!

Long time reader of this site, but currently I cannot find a solution to my problem.

For a little background info this table is a planned amount of events at each location per month. And the formula is reading this table and transferring the information into a new list on another worksheet, thus this table cannot be sorted and the output list (formula) should return the outputs from left to right. An Array formula would be acceptable, but I do not want to use VBA as I am not well versed in them and I like the instant and live results of formulas. Also because this workbook will be sent out to Excel users who have very little knowledge and must be automatic, efficient and work seamlessly in the background with no other involvement than predefined inputs.

Named Range (A15:G20) = Events, Located on Sheet1
​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Redmond[/TD]
[TD]Beijing[/TD]
[TD]Dublin[/TD]
[TD]Hyderabad[/TD]
[TD]Munich[/TD]
[TD]Singapore[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Jul[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Aug[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Sep[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Oct[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Nov[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Desired Results - Located on Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jul[/TD]
[TD]Redmond[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]

Jul​

<strike></strike>[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]

Jul​

<strike></strike>[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jul[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jul[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jul[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]<strike></strike>

Redmond​

<strike></strike>[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]Beijing[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]Dublin[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]Dublin[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]

Aug​

<strike></strike>[/TD]
[TD]Hyderabad[/TD]
[/TR]
</tbody>[/TABLE]

Here is my formula: =Index(Events, MATCH($C1,'Sheet1'!$A$16:$A$20,0), Match(COUNTIF($C$1:$C1,$C1),Sum(A15:G15),1))

​

Scenario: Column C is prepopulated by another formula. This new formula would be pasted in D1 and copied down, and return the Event locations (column headers) based on the count of how many times that month has appeared in Column C. If the count of months in Column C exceeds the planned number of events in the first column, then it will sum the next column until the sum is greater than or equal to the count of month.

Logic Check:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jul (Row=1,Count=1)[/TD]
[TD](Count <= Sum(col1) = col1)
(1 <= 6) =

Redmond​

[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]

Jul

(

Row=1,​

Count=2)​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(2 <= 6) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]

Jul

(

Row=1,​

Count=3)​

​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(3 <= 6) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jul

(

Row=1,​

Count=4)​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(4 <= 6) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jul

(

Row=1,​

Count=5)​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(5 <= 6) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jul

(

Row=1,​

Count=6)​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(6 <= 6) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]

Aug

(Row=2,Count=1)​

​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(1 <= 3) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]

Aug

(Row=2,Count=2)​

​

​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(2 <= 3) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]

Aug

(Row=2,Count=3)​

​

[/TD]
[TD]<strike></strike>

(Count <= Sum(col1) = col1)

(3 <= 3) = Redmond​

<strike></strike>
​

​

[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]

Aug

(Row=2,Count=4)​

​

[/TD]
[TD]

(Count > Sum(col1) Therefore (Count <= Sum(col1+col2))

(4 > 3) Therefore (4 <= 3+1) = Beijing​

​

[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]

Aug

(Row=2,Count=5)​

​

[/TD]
[TD]

(Count > Sum(col1+col2)) Therefore

(Count <= Sum(col1+col2+col3))​

​

<strike></strike>

(5 > 3+1) Therefore (5 <= 3+1+2) = ​

Dublin[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]

Aug

(Row=2,Count=6)​

​

[/TD]
[TD]

(Count <= Sum(col1+col2+col3))
(6 <= 3+1+2) = ​

Dublin[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]

Aug

(Row=2,Count=7)​

​

[/TD]
[TD]

(Count > Sum(col1+col2+col3)) Therefore (Count <= Sum(col1+col2+col3+col4))
(7 <= 3+1+2+2) = ​

Hyderabad[/TD]
[/TR]
</tbody>[/TABLE]
And so on down the list...​

 

[Marcelo Branco]

Maybe...

To make things easier create a helper column in Sheet1 like below (gray area)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD]

Redmond​

[/TD]
[TD]

Beijing​

[/TD]
[TD]

Dublin​

[/TD]
[TD]

Hyderabad​

[/TD]
[TD]

Munich​

[/TD]
[TD]

Singapore​

[/TD]
[TD][/TD]
[TD]

Total
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

Jul​

[/TD]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

6
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Aug​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

16​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

Sep​

[/TD]
[TD]

8​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

25​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

Oct​

[/TD]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

38
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

Nov​

[/TD]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

45​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in I16 copied down
=SUM(B$16:G16)


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Jul​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Jul​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Jul​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Jul​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Jul​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Jul​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Beijing​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Dublin​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Dublin
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Hyderabad
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Hyderabad​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Munich​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

Aug​

[/TD]
[TD]

Munich​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

Sep​

[/TD]
[TD]

Singapore​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

29​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

30​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

31​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

32​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Beijing​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

33​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Dublin
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

34​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Dublin
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

35​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Hyderabad​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

36​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Hyderabad​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

37​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Munich​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

38​

[/TD]
[TD]

Oct​

[/TD]
[TD]

Munich​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

39​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

40​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Redmond
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

41​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

42​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

43​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

44​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Redmond​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

45​

[/TD]
[TD]

Nov​

[/TD]
[TD]

Singapore​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

46​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Formula in C1 copied down
=IF(ROWS(C$1:C1)>Sheet1!$I$20,"",INDEX(Sheet1!A$16:A$20,IFERROR(1+MATCH(ROWS(C$1:C1)-1,Sheet1!I$16:I$20),1)))

Array formula in D1 copied down
=IF(C1="","",INDEX(Sheet1!B$15:G$15,MATCH(COUNTIF(C$1:C1,C1)-1,SUBTOTAL(9,OFFSET(INDEX(Sheet1!$A$16:$A$20,MATCH(C1,Sheet1!$A$16:$A$20,0)),,,1,COLUMN($A$1:$G$1)-COLUMN($A$1)+1)))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[mkrecknagel]

Thank you Marcelo! I will give it a try, looks a lot better than what I was able to come up with. I eventually ended up with a helper column as well. Thank you for going above and beyond by providing the C1 formula in addition to the D1 formula!