INDEX MATCH

[rservice0320]

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

[#2 · Accepted solution]

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

[#3]

Han, isn't the "*" trying to multiply?

[#4]

never saw it used like this, but it works.. thank you so much!!

[#5]

i tried it on my personal laptop & it worked. when it did it on my work laptop, i got a "spill" error...can you help me with that

[#6]

@rservice0320 

I don't see why it would cause a spill error. Could you attach a sample workbook demonstrating the problem, or make it available through OneDrive, Google Drive or Dropbox?

[#7]

can you help me with what a spill error is?

[#8]

@rservice0320 

That's here How to correct a #SPILL! error (microsoft.com)

[#9]

Hans, it was user error. It is working now. I do have a new index match issue I am hoping you can help me with. I posted it as a new discussion.