Index Array Formula Assistance Request

[VRGood]

Hello all,

I am using Excel 2016, 64 bit. I am having difficulty with a formula. I'm trying to extract a unique list of names based upon a criteria. My formula is pulling only the first two names in the list.

My formula is:

{=INDEX(Table3[FullName],MATCH(0,IF($D$2=Table3[Classroom], COUNTIF($E1:$E1,Table3[FullName]),""),0))}

Sample data:
UniqueList contains the above formula and current results are shown below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Classroom[/TD]
[TD]Full Name[/TD]
[TD]Blank[/TD]
[TD]Criteria[/TD]
[TD]UniqueList[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name1[/TD]
[TD][/TD]
[TD]Classroom 9[/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Classroom 50[/TD]
[TD]Name4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Classroom 9[/TD]
[TD]Name3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated.
Thank you.

 

[Marcelo Branco]

Missing $

Try
=INDEX(Table3[FullName],MATCH(0,IF($D$2=Table3[Classroom], COUNTIF($E$1:$E1,Table3[FullName]),""),0))
Ctrl+Shift+Enter

M.

 

[VRGood]

Thank you! I figured it was something simple, but I just couldn't find it. Have a wonderful day!

 

[Marcelo Branco]

You are welcome.

M.

 

[anderma8]

Ironically- I too am trying to leverage an array within an IF statement but nothing works. I have the following:

{=VLOOKUP($A5&$B5&$C5,CHOOSE({1,2,3},'2017BaysisSourceData'!$D$2:$D$730&'2017BaysisSourceData'!$E$2:$E$730&'2017BaysisSourceData'!$F$2:$F$730,'2017BaysisSourceData'!$M$2:$M$730),2,FALSE)}

Which works - returning the expected value. I'm trying to ONLY display the value IF it's before a certain month. For example if the month is May, I do not want June-December to populate.

I've been trying different tactics but nothing works. For the month comparison, I'm using =IF(MAX($A$1,E$3)=$A$1,D3,"") but obviously this does not leverage the array. logically, I'd insert the array formula in lieu of D3. Any suggestions?

 

[Marcelo Branco]

Suggestions?

Assuming there are dates in the searched column (range1 in the formula below) and A1 contains 5 (May) i would try something like this
=INDEX(range1,MATCH(1,IF((range2=A5)*(range3=B5)*(range4=C5)*(MONTH(range1)<=A1),1),0))
Ctrl+Shift+Enter

Considering your formula above, range1 would be
'2017BaysisSourceData'!$M$2:$M$730

By the way, in your formula you can change
CHOOSE({1,2,3},.....)
by
CHOOSE({1,2},.....)

M.

 

[Marcelo Branco]

A little bit shorter formula
=INDEX(range1,MATCH(1,(range2=A5)*(range2=B5)*(range3=C5)*(MONTH(range1)<=A1),0))
Ctrl+Shift+Enter

M.

 

[anderma8]

THANKS for your feedback, unfortunately I don't quite understand it.... I've tried to adjust both suggestions but it's not working .... yet.... Here is the data:

[TABLE="width: 454"]
<tbody>[TR]
[TD]month
[/TD]
[TD]town
[/TD]
[TD]Street
[/TD]
[TD]Division
[/TD]
[TD]Number of Contacts
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Florham Park
[/TD]
[TD]Main Street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Florham Park
[/TD]
[TD]North street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]50
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Florham Park
[/TD]
[TD]South Street
[/TD]
[TD]Area 2
[/TD]
[TD="align: right"]66
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Madison
[/TD]
[TD]Hot street
[/TD]
[TD]Area 3
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Madison
[/TD]
[TD]cold street
[/TD]
[TD]Area 4
[/TD]
[TD="align: right"]20
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Madison
[/TD]
[TD]cross street
[/TD]
[TD]Area 5
[/TD]
[TD="align: right"]43
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Florham Park
[/TD]
[TD]Main Street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Florham Park
[/TD]
[TD]North street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]51
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Florham Park
[/TD]
[TD]South Street
[/TD]
[TD]Area 2
[/TD]
[TD="align: right"]60
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Madison
[/TD]
[TD]Hot street
[/TD]
[TD]Area 3
[/TD]
[TD="align: right"]15
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Madison
[/TD]
[TD]cold street
[/TD]
[TD]Area 4
[/TD]
[TD="align: right"]23
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]Madison
[/TD]
[TD]cross street
[/TD]
[TD]Area 5
[/TD]
[TD="align: right"]45
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Florham Park
[/TD]
[TD]Main Street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]44
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Florham Park
[/TD]
[TD]North street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]55
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Florham Park
[/TD]
[TD]South Street
[/TD]
[TD]Area 2
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Madison
[/TD]
[TD]Hot street
[/TD]
[TD]Area 3
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Madison
[/TD]
[TD]cold street
[/TD]
[TD]Area 4
[/TD]
[TD="align: right"]24
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD]Madison
[/TD]
[TD]cross street
[/TD]
[TD]Area 5
[/TD]
[TD="align: right"]38
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Florham Park
[/TD]
[TD]Main Street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]59
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Florham Park
[/TD]
[TD]North street
[/TD]
[TD]Area 1
[/TD]
[TD="align: right"]48
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Florham Park
[/TD]
[TD]South Street
[/TD]
[TD]Area 2
[/TD]
[TD="align: right"]44
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Madison
[/TD]
[TD]Hot street
[/TD]
[TD]Area 3
[/TD]
[TD="align: right"]15
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Madison
[/TD]
[TD]cold street
[/TD]
[TD]Area 4
[/TD]
[TD="align: right"]25
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD]Madison
[/TD]
[TD]cross street
[/TD]
[TD]Area 5
[/TD]
[TD="align: right"]39
[/TD]
[/TR]
</tbody>[/TABLE]


When the month changes to February, the data should look like this:
[TABLE="width: 864"]
<tbody>[TR]
[TD="class: xl67, width: 72, bgcolor: yellow"]February
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]town
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]Street
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]Division
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]January
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]February
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]March
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]April
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]May
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]June
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]….
[/TD]
[TD="class: xl68, width: 72, bgcolor: #CCFFCC"]Average
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Florham Park
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Main Street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area 1
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]50
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]52
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Florham Park
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]North
street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area
1
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]50
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]51
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]50.5
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Florham Park
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]South
Street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area
2
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]66
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]60
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]63
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Madison
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Hot
street
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Area
3
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]5
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]15
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Madison
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]cold
street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area
4
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]20
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]23
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]21.5
[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Madison
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]cross
street
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Area
5
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]43
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]45
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]44
[/TD]
[/TR]
</tbody>[/TABLE]
and when they change it to April, it should look like this:

[TABLE="width: 864"]
<tbody>[TR]
[TD="class: xl68, width: 72, bgcolor: yellow"]April
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]town
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]Street
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]Division
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]January
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]February
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]March
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]April
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]May
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]June
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]….
[/TD]
[TD="class: xl69, width: 72, bgcolor: #CCFFCC"]Average
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Florham Park
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Main Street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area 1
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]50
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]52
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]44
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]59
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]51.25
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Florham Park
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]North
street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area
1
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]50
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]51
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]55
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]48
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]51
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Florham Park
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]South
Street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area
2
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]66
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]60
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]52
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]44
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]55.5
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Madison
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Hot
street
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Area
3
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]5
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]15
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]11
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]15
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]11.5
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Madison
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]cold
street
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"]Area
4
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]20
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]23
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]24
[/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]25
[/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white"][/TD]
[TD="class: xl65, width: 72, bgcolor: white, align: right"]23
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Madison
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]cross
street
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"]Area
5
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]43
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]45
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]38
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]39
[/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC"][/TD]
[TD="class: xl66, width: 72, bgcolor: #FFFFCC, align: right"]41.25
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry for the copy/paste. I have a file but could not find how to upload it. I could make a pivot chart, but I wouldn't be able to leverage the output in other lists.....

Thank you for your time and attention!

Mark

 

[Marcelo Branco]

Assuming (try to adapt to your actual scenario)
Data in Sheet1 columns A:E;
headers in row 1,
data in rows 2:1000

Maybe something like this on another sheet


[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]
[TD="bgcolor: #DCE6F1"]

J​

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

1​

[/TD]
[TD]

2​

[/TD]
[TD]

town​

[/TD]
[TD]

Street​

[/TD]
[TD]

Division​

[/TD]
[TD]

January​

[/TD]
[TD]

February​

[/TD]
[TD]

March​

[/TD]
[TD]

April​

[/TD]
[TD]

May​

[/TD]
[TD]

June​

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

2​

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

Florham Park​

[/TD]
[TD]

Main Street​

[/TD]
[TD]

Area 1​

[/TD]
[TD]

50​

[/TD]
[TD]

52​

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

3​

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

Florham Park​

[/TD]
[TD]

North street​

[/TD]
[TD]

Area 1​

[/TD]
[TD]

50​

[/TD]
[TD]

51​

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

4​

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

Florham Park​

[/TD]
[TD]

South Street​

[/TD]
[TD]

Area 2​

[/TD]
[TD]

66​

[/TD]
[TD]

60​

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

5​

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

Madison​

[/TD]
[TD]

Hot street​

[/TD]
[TD]

Area 3​

[/TD]
[TD]

5​

[/TD]
[TD]

15​

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

6​

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

Madison​

[/TD]
[TD]

cold street​

[/TD]
[TD]

Area 4​

[/TD]
[TD]

20​

[/TD]
[TD]

23​

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

7​

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

Madison​

[/TD]
[TD]

cross street​

[/TD]
[TD]

Area 5​

[/TD]
[TD]

43​

[/TD]
[TD]

45​

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


Observe the 2 in A1 (not February)

Array formula in E2 copied across and down
=IF(COLUMNS($E2:E2)<=$A$1,INDEX(Sheet1!$E$2:$E$1000,MATCH(1,(Sheet1!$A$2:$A$1000=COLUMNS($E2:E2))*(Sheet1!$B$2:$B$1000=$B2)*(Sheet1!$C$2:$C$1000=$C2)*(Sheet1!$D$2:$D$1000=$D2),0)),"")
Ctrl+Shift+Enter

Then change A1 to 4 to get the results till April

M.

 

[anderma8]

It took a little bit of work to convert it but IT WORKS EXACTLY as expected - MUCH appreciated!! Wishing you a VERY Happy New Years 2018!! I LOVE this forum - I keep learning new things!!