Index Match , three conditions, Excel 2016

[Yecart77]

Hi All,

Using Excel 2016 for this task that I need some guidance on to see if I can get index match to work. I have given it a go but cannot make it work.

The table in B2:O10 is an IBM Planning Analytics custom report that has values generated from DBRW formulas and is refreshed regularly.

The table in C15:O19 is to drive a chart.

If you select a different branch from the drop down in C14 the data in D16:O19 will update if it satisfies three conditions, using cell D16 as an example.
1. The value in C14 'Branch A' finds an exact match in the range B3:B10.
2. The value in C16 'Current Budget' find an exact match in the range C3:C10; and
3. The value in D15 'Jul" finds and exact match in the range D2:O2.

Thank you in advance for any suggestions, it is greatly appreciated and will end my frustration.

Cheers,
Tracey

 

[kevin9999]

I changed the values for Branch B to demonstrate the difference. In future, you could help the volunteers by providing a copy of your data using the XL2BB add in, or alternatively, sharing your file via Google Drive, Dropbox or similar file sharing platform to prevent helpers having to type out your data to test formulas/code etc.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Branch name	Version	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
3		Branch A	Current Budget	35000	30000	26000	18000	15000	15000	21000	32000	33000	45000	38000	36000
4		Branch A	Actual	34914	31234	28569
5		Branch A	Commitment
6		Branch A	Forecast				18000	15000	15000	21000	32000	33000	45000	38000	36000
7		Branch B	Current Budget	34000	29000	25000	17000	14000	14000	20000	31000	32000	44000	37000	35000
8		Branch B	Actual	33914	30234	27569
9		Branch B	Commitment
10		Branch B	Forecast				17000	14000	14000	20000	31000	32000	44000	37000	35000
11
12
13			select
14			Branch A
15			Version	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
16			Current Budget	35000	30000	26000	18000	15000	15000	21000	32000	33000	45000	38000	36000
17			Actual	34914	31234	28569	0	0	0	0	0	0	0	0	0
18			Commitment	0	0	0	0	0	0	0	0	0	0	0	0
19			Forecast	0	0	0	18000	15000	15000	21000	32000	33000	45000	38000	36000
Sheet1

Cell Formulas
Range	Formula
D16:O19	D16	=SUMPRODUCT(($B$3:$B$10=$C$14)*($D$2:$O$2=D$15)*($C$3:$C$10=$C16),$D$3:$O$10)

Cells with Data Validation
Cell	Allow	Criteria
C14	List	Branch A,Branch B

 

[Alex Blakenburg]

Taking advantage of @kevin9999 kindly setting up some sample data , this is variation on using Index Match.
PS: I have the formula spread over 3 lines so you might need to make your formula bar bigger.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Branch name	Version	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
3		Branch A	Current Budget	35000	30000	26000	18000	15000	15000	21000	32000	33000	45000	38000	36000
4		Branch A	Actual	34914	31234	28569
5		Branch A	Commitment
6		Branch A	Forecast				18000	15000	15000	21000	32000	33000	45000	38000	36000
7		Branch B	Current Budget	34000	29000	25000	17000	14000	14000	20000	31000	32000	44000	37000	35000
8		Branch B	Actual	33914	30234	27569
9		Branch B	Commitment
10		Branch B	Forecast				17000	14000	14000	20000	31000	32000	44000	37000	35000
11
12
13			select
14			Branch A
15			Version	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
16			Current Budget	35000	30000	26000	18000	15000	15000	21000	32000	33000	45000	38000	36000
17			Actual	34914	31234	28569	0	0	0	0	0	0	0	0	0
18			Commitment	0	0	0	0	0	0	0	0	0	0	0	0
19			Forecast	0	0	0	18000	15000	15000	21000	32000	33000	45000	38000	36000
Mine

Cell Formulas
Range	Formula
D16:O19	D16	=SUMIFS(INDEX($D$3:$O$10,0,MATCH(D$15,$D$2:$O$2,0)), $C$3:$C$10,$C16, $B$3:$B$10,$C$14)