Can someone help me with the following formula? =INDEX($C$4:$G$7,MATCH($A11,$A$4:$A$7,0),MATCH($B11,$B$4:$B$7,0),MATCH($C11,INDEX($C$1:$G$1,0),MATCH($E11&$D11,INDEX($C$2:$G$2&$C$3:$G$3,0)))) ActualActualActualActualActual 20182018201820192019 OctNovDecJanFebBrazilSales 500,000 600,000 700,000 800,000 900,000MexicoSales 200,000 400,000 600,000 80,000 1,000,000BrazilAR 60,000 80,000 90,000 10,000 45,000MexicoAR 70,000 90,000 75,000 65,000 55,000 BUDataTypemonthyearamt BrazilSalesActualOct2018#REF! MexicoARActualOct2018#REF! BrazilSalesActualOct2018#REF! When I then try to "simplify" it, it posts the incorrect error (see below):=INDEX($C$4:$G$7,MATCH(A11,$A$4:$A$7,0),MATCH($E11,INDEX($C$2:$G$2,0),MATCH($D$11,INDEX($C$3:$G$3,0),MATCH($C11,INDEX($C$1:$G$1,0),MATCH($B11,$B$4:$B$7,0))))) ActualActualActualActualActual 20182018201820192019 OctNovDecJanFebBrazilSales 500,000 600,000 700,000 800,000 900,000MexicoSales 200,000 400,000 600,000 80,000 1,000,000BrazilAR 60,000 80,000 90,000 10,000 45,000MexicoAR 70,000 90,000 75,000 65,000 55,000 BUDataTypemonthyearamt BrazilSalesActualOct2018700000 MexicoARActualOct2018600000 BrazilSalesActualNov2018700000 Thank you!!

rservice0320 In F11: =INDEX($C$4:$G$7,MATCH(1,($A$4:$A$7=A11)*($B$4:$B$7=B11),0),MATCH(1,($C$1:$G$1=C11)*($C$2:$G$2=E11)*($C$3:$G$3=D11),0)) If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter. Then fill down.

rservice0320

Brass Contributor

Apr 05, 2022

Solved

INDEX MATCH

Can someone help me with the following formula?

=INDEX($C$4:$G$7,MATCH($A11,$A$4:$A$7,0),MATCH($B11,$B$4:$B$7,0),MATCH($C11,INDEX($C$1:$G$1,0),MATCH($E11&$D11,INDEX($C$2:$G$2&$C$3:$G$3,0))))

		Actual	Actual	Actual	Actual	Actual
		2018	2018	2018	2019	2019
		Oct	Nov	Dec	Jan	Feb
Brazil	Sales	500,000	600,000	700,000	800,000	900,000
Mexico	Sales	200,000	400,000	600,000	80,000	1,000,000
Brazil	AR	60,000	80,000	90,000	10,000	45,000
Mexico	AR	70,000	90,000	75,000	65,000	55,000


BU	Data	Type	month	year	amt
Brazil	Sales	Actual	Oct	2018	#REF!
Mexico	AR	Actual	Oct	2018	#REF!
Brazil	Sales	Actual	Oct	2018	#REF!

When I then try to "simplify" it, it posts the incorrect error (see below):

=INDEX($C$4:$G$7,MATCH(A11,$A$4:$A$7,0),MATCH($E11,INDEX($C$2:$G$2,0),MATCH($D$11,INDEX($C$3:$G$3,0),MATCH($C11,INDEX($C$1:$G$1,0),MATCH($B11,$B$4:$B$7,0)))))

		Actual	Actual	Actual	Actual	Actual
		2018	2018	2018	2019	2019
		Oct	Nov	Dec	Jan	Feb
Brazil	Sales	500,000	600,000	700,000	800,000	900,000
Mexico	Sales	200,000	400,000	600,000	80,000	1,000,000
Brazil	AR	60,000	80,000	90,000	10,000	45,000
Mexico	AR	70,000	90,000	75,000	65,000	55,000


BU	Data	Type	month	year	amt
Brazil	Sales	Actual	Oct	2018	700000
Mexico	AR	Actual	Oct	2018	600000
Brazil	Sales	Actual	Nov	2018	700000

Thank you!!

Formulas and Functions

HansVogelaar
Apr 05, 2022
rservice0320

In F11:

=INDEX($C$4:$G$7,MATCH(1,($A$4:$A$7=A11)*($B$4:$B$7=B11),0),MATCH(1,($C$1:$G$1=C11)*($C$2:$G$2=E11)*($C$3:$G$3=D11),0))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

Then fill down.

Forum Discussion

INDEX MATCH

8 Replies

Resources