How to use Index Match with Multiple Conditions in the same Column

[shablagoo]

Hi,

I am looking for a way to write an Index Match formula such that two match conditions in the same column are met. In the example below I want to fetch the value for Alpha Margins for Q1 2017. The first column should look for Alpha but in the second column it should look for both "Margins" and "Q1 2017".

Also open to any other way to get this.

Any help would be appreciated.

Alpha
Margins	Q1	Q2	Q3	Q4
FY 2017	?	?	?	?
FY 2018	?	?	?	?
FY 2019	?	?	?	?
	FY 2017
	Revenue	Revenue	Revenue	Revenue
	Q1 2017	Q2 2017	Q3 2017	Q4 2017
Alpha	25	30	35	40
Beta	30	35	40	45
Gamma	35	40	45	50
Delta	40	45	50	55
	Costs	Costs	Costs	Costs
	Q1 2017	Q2 2017	Q3 2017	Q4 2017
Alpha	10	13	16	19
Beta	13	16	19	22
Gamma	16	19	22	25
Delta	19	22	25	28
	Profit	Profit	Profit	Profit
	Q1 2017	Q2 2017	Q3 2017	Q4 2017
Alpha	15	15	15	15
Beta	15	15	15	15
Gamma	15	15	15	15
Delta	15	15	15	15
	Margin	Margin	Margin	Margin
	Q1 2017	Q2 2017	Q3 2017	Q4 2017
Alpha	60%	60%	60%	60%
Beta	60%	60%	60%	60%
Gamma	60%	60%	60%	60%
Delta	60%	60%	60%	60%

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to use Index Match with Multiple Conditions in the same Column
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DanteAmor]

Check on your sheet, "margin" in cells A3 and B33 must have the same text.

Dante Amor
	A	B	C	D	E
1	Alpha
2
3	Margin	Q1	Q2	Q3	Q4
4
5	2017	60%	50%	43%	38%
6	2018
7	2019
8
9		2017
10		Revenue	Revenue	Revenue	Revenue
11		Q1 2017	Q2 2017	Q3 2017	Q4 2017
12	Alpha	25	30	35	40
13	Beta	30	35	40	45
14	Gamma	35	40	45	50
15	Delta	40	45	50	55
16
17
18		Costs	Costs	Costs	Costs
19		Q1 2017	Q2 2017	Q3 2017	Q4 2017
20	Alpha	10	13	16	19
21	Beta	13	16	19	22
22	Gamma	16	19	22	25
23	Delta	19	22	25	28
24
25		Profit	Profit	Profit	Profit
26		Q1 2017	Q2 2017	Q3 2017	Q4 2017
27	Alpha	15	15	15	15
28	Beta	15	15	15	15
29	Gamma	15	15	15	15
30	Delta	15	15	15	15
31
32
33		Margin	Margin	Margin	Margin
34		Q1 2017	Q2 2017	Q3 2017	Q4 2017
35	Alpha	60%	50%	43%	38%
36	Beta	50%	43%	38%	33%
37	Gamma	43%	38%	33%	30%
38	Delta	38%	33%	30%	27%
Sheet1

Cell Formulas
Range	Formula
B5:E7	B5	=IFERROR(VLOOKUP($A$1,INDEX($A$12:$A$38,MATCH($A$3,$B$10:$B$38,0)):E39,MATCH(B$3&" "&$A5,$B$11:$E$11,0)+1,0),"")
B35:E38	B35	=B27/B12