Index & Match - #REF! error

[Rakeweru]

Hi,

Am trying out the Index + Match formula but received some error messages below:

	Column A	Column B	Column C	Column D	Column E
Row 4	Month (Formula)	Month (Formula)	Branch	Region (Formula)	Region (Value displayed)
Row 5	43466	43466	A	=INDEX(J$3:J$14,MATCH(B5,G$3:G$14,0),MATCH(C5,I$3:I$14,0))	Alpha
Row 6	43466	43466	B	=INDEX(J$3:J$14,MATCH(B6,G$3:G$14,0),MATCH(C6,I$3:I$14,0))	#REF !
Row 7	43497	43497	C	=INDEX(J$3:J$14,MATCH(B7,G$3:G$14,0),MATCH(C7,I$3:I$14,0))	#REF !
Row 8	43525	43525	D	=INDEX(J$3:J$14,MATCH(B8,G$3:G$14,0),MATCH(C8,I$3:I$14,0))	#REF !
Row 9	43525	43525	E	=INDEX(J$3:J$14,MATCH(B9,G$3:G$14,0),MATCH(C9,I$3:I$14,0))	#REF !

<tbody fgid="43670">
</tbody>

	Column G	Column H	Column I	Column J
Row 2	Month (Formula)	Month (Value Displayed)	Branch	Region
Row 3	43466	43466	A	Alpha
Row 4	43466	43466	B	Beta
Row 5	43466	43466	C	Charlie
Row 6	43466	43466	D	Delta
Row 7	43497	43497	A	Alpha
Row 8	43497	43497	B	Beta 2
Row 9	43497	43497	C	Charlie 3
Row 10	43497	43497	D	Delta
Row 11	43525	43525	A	Alpha
Row 12	43525	43525	B	Beta
Row 13	43525	43525	C	Charlie
Row 14	43525	43525	D	Delta 5

<tbody fgid="42993">
</tbody>

I managed to get a successful hit for Column E, Row 5, but subsequent rows (6-9) shows #REF ! errors.

Hope someone can help to troubleshoot and provide guidance, thanks! :D

 

[AhoyNC]

In the INDEX / MATCH formula the second MATCH is for a column match not row as you are trying to use it.
INDEX(J$3:J$14,MATCH(B5,G$3:G$14,0),MATCH(C5,I$3:I$14,0))

Maybe something like this array formula that must be entered with CTRL-SHIFT-ENTER. Then drag formula down.
NOTE #NA is because there is no match for Branch E.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1							Column G	Column H	Column I	Column J
2							Month (Formula)	Month (Value Displayed)	Branch	Region
3	Column A	Column B	Column C	Column D			1/1/2019	1/1/2019	A	Alpha
4	Month (Formula)	Month (Formula)	Branch	Region (Formula)			1/1/2019	1/1/2019	B	Beta
5	1/1/2019	1/1/2019	A	Alpha			1/1/2019	1/1/2019	C	Charlie
6	1/1/2019	1/1/2019	B	Beta			1/1/2019	1/1/2019	D	Delta
7	2/1/2019	2/1/2019	C	Charlie 3			2/1/2019	2/1/2019	A	Alpha
8	3/1/2019	3/1/2019	D	Delta 5			2/1/2019	2/1/2019	B	Beta 2
9	3/1/2019	3/1/2019	E	#N/A			2/1/2019	2/1/2019	C	Charlie 3
10							2/1/2019	2/1/2019	D	Delta
11							3/1/2019	3/1/2019	A	Alpha
12							3/1/2019	3/1/2019	B	Beta
13							3/1/2019	3/1/2019	C	Charlie
14							3/1/2019	3/1/2019	D	Delta 5
Sheet

 

[Rakeweru]

The formula works! Thank you so much :D !!!