Index match conditional

[afrobea_r]

Dear Excel Community,

Currently I have a parameters table that looks like this.

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Type[/TD]
[TD="class: xl64, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]AB[/TD]
[TD="class: xl64, width: 64"]A[/TD]
[/TR]
[TR]
[TD="class: xl63"]Dispatch[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Days[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]28[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63, align: right"]31[/TD]
[TD="class: xl63, align: right"]30[/TD]
[TD="class: xl63, align: right"]31[/TD]
[/TR]
[TR]
[TD="class: xl63"]Months[/TD]
[TD="class: xl65, align: right"]Jan-18[/TD]
[TD="class: xl65, align: right"]Feb-18[/TD]
[TD="class: xl65, align: right"]Mar-18[/TD]
[TD="class: xl65, align: right"]Apr-18[/TD]
[TD="class: xl65, align: right"]May-18[/TD]
[TD="class: xl65, align: right"]Jun-18[/TD]
[TD="class: xl65, align: right"]Jul-18[/TD]
[TD="class: xl65, align: right"]Aug-18[/TD]
[TD="class: xl65, align: right"]Sep-18[/TD]
[TD="class: xl65, align: right"]Oct-18[/TD]
[TD="class: xl65, align: right"]Nov-18[/TD]
[TD="class: xl65, align: right"]Dec-18[/TD]
[/TR]
</tbody>[/TABLE]

How do I utilize index match function to return the dispatch values based on their types onto another table in another sheet that looks like this

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64, align: right"]Jan-18[/TD]
[TD="class: xl65, width: 64, align: right"]Feb-18[/TD]
[TD="class: xl65, width: 64, align: right"]Mar-18[/TD]
[TD="class: xl65, width: 64, align: right"]Apr-18[/TD]
[TD="class: xl65, width: 64, align: right"]May-18[/TD]
[TD="class: xl65, width: 64, align: right"]Jun-18[/TD]
[TD="class: xl65, width: 64, align: right"]Jul-18[/TD]
[TD="class: xl65, width: 64, align: right"]Aug-18[/TD]
[TD="class: xl65, width: 64, align: right"]Sep-18[/TD]
[TD="class: xl65, width: 64, align: right"]Oct-18[/TD]
[TD="class: xl65, width: 64, align: right"]Nov-18[/TD]
[TD="class: xl65, width: 64, align: right"]Dec-18[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]AB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

At the moment, the formula I have just gives me the same values as the first table under the dispatch row regardless if it is A or AB?

Thank you all in advance!

 

[Peter_SSs]

Is this what you mean? If not, please give the expected results for your sample data and explain why they are the results.

Formula in B8 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type	AB	A	AB	A	AB	A	AB	A	AB	A	AB	A
2	Dispatch	50	10	50	10	50	10	50	10	50	2	10	2
3	Days	31	28	31	30	31	30	31	31	30	31	30	31
4	Months	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
5
6
7		Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
8	A		10		10		10		10		2		2
9	AB	50		50		50		50		50		10
Dispatch Values

 

[Marcelo Branco]

EDIT: Peter has already provided a solution

Maybe something like this (adjust the ranges as needed)


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

K​

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

L​

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

M​

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

1​

[/TD]
[TD]

Type​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

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

2​

[/TD]
[TD]

Dispatch​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

2​

[/TD]
[TD]

10​

[/TD]
[TD]

2​

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

3​

[/TD]
[TD]

Days​

[/TD]
[TD]

31​

[/TD]
[TD]

28​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

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

4​

[/TD]
[TD]

Months​

[/TD]
[TD]

Jan-18​

[/TD]
[TD]

Feb-18​

[/TD]
[TD]

Mar-18​

[/TD]
[TD]

Apr-18​

[/TD]
[TD]

May-18​

[/TD]
[TD]

Jun-18​

[/TD]
[TD]

Jul-18​

[/TD]
[TD]

Aug-18​

[/TD]
[TD]

Sep-18​

[/TD]
[TD]

Oct-18​

[/TD]
[TD]

Nov-18​

[/TD]
[TD]

Dec-18​

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

5​

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

6​

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

7​

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

8​

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

Jan-18​

[/TD]
[TD]

Feb-18​

[/TD]
[TD]

Mar-18​

[/TD]
[TD]

Apr-18​

[/TD]
[TD]

May-18​

[/TD]
[TD]

Jun-18​

[/TD]
[TD]

Jul-18​

[/TD]
[TD]

Aug-18​

[/TD]
[TD]

Sep-18​

[/TD]
[TD]

Oct-18​

[/TD]
[TD]

Nov-18​

[/TD]
[TD]

Dec-18​

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

9​

[/TD]
[TD]

A​

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

10​

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

10​

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

10​

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

10​

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

2​

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

2​

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

10​

[/TD]
[TD]

AB​

[/TD]
[TD]

50​

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

50​

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

50​

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

50​

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

50​

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

10​

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


Formula in B9 copied across and down
=IF(COUNTIFS($B$1:$M$1,$A9,$B$4:$M$4,B$8),INDEX($B$2:$M$2,MATCH(B$8,$B$4:$M$4,0)),"")

M.

 

[jtakw]

Hi,

Why not just use SUMIFS:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type	AB	A	AB	A	AB	A	AB	A	AB	A	AB	A
2	Dispatch	50	10	50	10	50	10	50	10	50	2	10	2
3	Days	31	28	31	30	31	30	31	31	30	31	30	31
4	Months	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
5
6
7		Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
8	A	0	10	0	10	0	10	0	10	0	2	0	2
9	AB	50	0	50	0	50	0	50	0	50	0	10	0
10
11
12		Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
13	A		10		10		10		10		2		2
14	AB	50		50		50		50		50		10
Sheet17

Cell Formulas
Range	Formula
B8	=SUMIFS($B$2:$M$2,$B$1:$M$1,$A8,$B$4:$M$4,B$7)
B13	=IF(SUMIFS($B$2:$M$2,$B$1:$M$1,$A13,$B$4:$M$4,B$7)=0,"",SUMIFS($B$2:$M$2,$B$1:$M$1,$A13,$B$4:$M$4,B$7))

Formula copied down and across.

 

[Peter_SSs]

There are many alternatives ..

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type	AB	A	AB	A	AB	A	AB	A	AB	A	AB	A
2	Dispatch	50	10	50	10	50	10	50	10	50	2	10	2
3	Days	31	28	31	30	31	30	31	31	30	31	30	31
4	Months	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
5
6
7		Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
8	A		10		10		10		10		2		2
9	AB	50		50		50		50		50		10
Dispatch Values (2)

 

[afrobea_r]

Thank you Sir! This worked like magic!

Is this what you mean? If not, please give the expected results for your sample data and explain why they are the results.

Formula in B8 is copied across and down.

Dispatch Values

A

B

C

D

E

F

G

H

I

J

K

L

M

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:70px;"><col style="width:57px;"><col style="width:59px;"><col style="width:59px;"><col style="width:57px;"><col style="width:62px;"><col style="width:57px;"><col style="width:53px;"><col style="width:60px;"><col style="width:60px;"><col style="width:58px;"><col style="width:58px;"><col style="width:60px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]A[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]Dispatch[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]31[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]Months[/TD]
[TD="align: center"]Jan-18[/TD]
[TD="align: center"]Feb-18[/TD]
[TD="align: center"]Mar-18[/TD]
[TD="align: center"]Apr-18[/TD]
[TD="align: center"]May-18[/TD]
[TD="align: center"]Jun-18[/TD]
[TD="align: center"]Jul-18[/TD]
[TD="align: center"]Aug-18[/TD]
[TD="align: center"]Sep-18[/TD]
[TD="align: center"]Oct-18[/TD]
[TD="align: center"]Nov-18[/TD]
[TD="align: center"]Dec-18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: center"]Jan-18[/TD]
[TD="align: center"]Feb-18[/TD]
[TD="align: center"]Mar-18[/TD]
[TD="align: center"]Apr-18[/TD]
[TD="align: center"]May-18[/TD]
[TD="align: center"]Jun-18[/TD]
[TD="align: center"]Jul-18[/TD]
[TD="align: center"]Aug-18[/TD]
[TD="align: center"]Sep-18[/TD]
[TD="align: center"]Oct-18[/TD]
[TD="align: center"]Nov-18[/TD]
[TD="align: center"]Dec-18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]A[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]50[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]50[/TD]

[TD="align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B8 =IF(INDEX($B$1:$M$1,MATCH(B$7,$B$4:$M$4,0))=$A8,INDEX($B$2:$M$2,MATCH(B$7,$B$4:$M$4,0)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[afrobea_r]

Thank you Sir for the additional workarounds!

EDIT: Peter has already provided a solution

Maybe something like this (adjust the ranges as needed)


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

K​

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

L​

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

M​

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

1​

[/TD]
[TD]

Type​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

[/TD]
[TD]

AB​

[/TD]
[TD]

A​

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

2​

[/TD]
[TD]

Dispatch​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

2​

[/TD]
[TD]

10​

[/TD]
[TD]

2​

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

3​

[/TD]
[TD]

Days​

[/TD]
[TD]

31​

[/TD]
[TD]

28​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

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

4​

[/TD]
[TD]

Months​

[/TD]
[TD]

Jan-18​

[/TD]
[TD]

Feb-18​

[/TD]
[TD]

Mar-18​

[/TD]
[TD]

Apr-18​

[/TD]
[TD]

May-18​

[/TD]
[TD]

Jun-18​

[/TD]
[TD]

Jul-18​

[/TD]
[TD]

Aug-18​

[/TD]
[TD]

Sep-18​

[/TD]
[TD]

Oct-18​

[/TD]
[TD]

Nov-18​

[/TD]
[TD]

Dec-18​

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

5​

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

6​

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

7​

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

8​

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

Jan-18​

[/TD]
[TD]

Feb-18​

[/TD]
[TD]

Mar-18​

[/TD]
[TD]

Apr-18​

[/TD]
[TD]

May-18​

[/TD]
[TD]

Jun-18​

[/TD]
[TD]

Jul-18​

[/TD]
[TD]

Aug-18​

[/TD]
[TD]

Sep-18​

[/TD]
[TD]

Oct-18​

[/TD]
[TD]

Nov-18​

[/TD]
[TD]

Dec-18​

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

9​

[/TD]
[TD]

A​

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

10​

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

10​

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

10​

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

10​

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

2​

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

2​

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

10​

[/TD]
[TD]

AB​

[/TD]
[TD]

50​

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

50​

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

50​

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

50​

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

50​

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

10​

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


Formula in B9 copied across and down
=IF(COUNTIFS($B$1:$M$1,$A9,$B$4:$M$4,B$8),INDEX($B$2:$M$2,MATCH(B$8,$B$4:$M$4,0)),"")

M.

 

[afrobea_r]

Thank you Sir for this as well!

Hi,

Why not just use SUMIFS:

	A	B	C	D	E	F	G	H	I	J	K	L	M
Type	AB	A	AB	A	AB	A	AB	A	AB	A	AB	A
Dispatch
Days
Months
A
AB
A
AB

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]10[/TD]

</tbody>

Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=SUMIFS($B$2:$M$2,$B$1:$M$1,$A8,$B$4:$M$4,B$7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=IF(SUMIFS($B$2:$M$2,$B$1:$M$1,$A13,$B$4:$M$4,B$7)=0,"",SUMIFS($B$2:$M$2,$B$1:$M$1,$A13,$B$4:$M$4,B$7))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down and across.

 

[Peter_SSs]

Thank you Sir! This worked like magic!

You are welcome. Glad to contribute a choice or two.