Index match conditional

[afrobea_r]

Dear Excel Community,

Currently I have a parameters table that looks like this.

Type	AB	A	AB	A	AB	A	AB	A	AB	A	AB	A
Dispatch	50	10	50	10	50	10	50	10	50	2	10	2
Days	31	28	31	30	31	30	31	31	30	31	30	31
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

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

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

Jan-18

Feb-18

Mar-18

Apr-18

May-18

Jun-18

Jul-18

Aug-18

Sep-18

Oct-18

Nov-18

Dec-18

A

AB

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

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)

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​

8​

Jan-18​

Feb-18​

Mar-18​

Apr-18​

May-18​

Jun-18​

Jul-18​

Aug-18​

Sep-18​

Oct-18​

Nov-18​

Dec-18​

9​

A​

10​

10​

10​

10​

2​

2​

10​

AB​

50​

50​

50​

50​

50​

10​

<tbody>
</tbody>

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

<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>
</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)

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​

8​

Jan-18​

Feb-18​

Mar-18​

Apr-18​

May-18​

Jun-18​

Jul-18​

Aug-18​

Sep-18​

Oct-18​

Nov-18​

Dec-18​

9​

A​

10​

10​

10​

10​

2​

2​

10​

AB​

50​

50​

50​

50​

50​

10​

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet17​

Worksheet Formulas Cell 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formula copied down and across.

 

[Peter_SSs]

Thank you Sir! This worked like magic!

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