Index match with multiple criteria?

[jimbogarner]

Hi,

Apologies as I am an excel noob and this forum is my go to!

I am trying to create a formula that looks up from a table with multiple criteria... for example; I want it to return -487,256 if all criteria is met, IE station 1, jul, ebitda, all cost centres, GL actual & 2020. but i want it in a table/lookup as the data will change and be updated

edit - doesn't need to be an index match if something else works, i've just used that for grids before.

Thanks,

James

 

[Crystalyzer]

=INDEX($E$13:$J$15,MATCH(1,($A$13:$A$15="Station 1")*($B$13:$B$15="Jul")*($C$13:$C$15="EBITDA")*($D$13:$D$15="All Cost Centers"),0),MATCH(1,($E$11:$J$11="GL Actual")*($E$12:$J$12=2020),0))

You can change the hard coded values (i.e. Station 1 etc) to cell references and then change the cell values to adjust what is returned.

I assumed there were additional columns for Forecast amounts for each of the years.

Book4
	A	B	C	D	E	F	G	H	I	J
1								-487256
2
3
4
5
6
7
8
9
10
11					GL Actual	GL Actual	GL Actual	Forecast	Forecast	Forecast
12	Stations	Week	P&L	CCs	2020	2021	2022	2020	2021	2022
13	Station 1	Jul	EBITDA	All Cost Centers	(487,256)	192,465	(1,138,877)
14	Station 1	Jul	Total Costs	All Cost Centers	1,962,134	2,897,286	2,653,580
15	Station 2	jul	EBITDA	All Cost Centers	587,474	1,714,648	1,093,283
16
17
Sheet1

Cell Formulas
Range	Formula
H1	H1	=INDEX($E$13:$J$15,MATCH(1,($A$13:$A$15="Station 1")*($B$13:$B$15="Jul")*($C$13:$C$15="EBITDA")*($D$13:$D$15="All Cost Centers"),0),MATCH(1,($E$11:$J$11="GL Actual")*($E$12:$J$12=2020),0))

 

[jimbogarner]

Genius! Worked perfectly, much appreciated!

 

[Crystalyzer]

Glad to help!