Index Match Error #REF

[SAXON10]

Hi,

I am apply the following index match formula in G3 but I get a error #ref .

G3=INDEX($D$3:$G$12,MATCH(J$2,$D$2:$G$2,0),MATCH($I3,$B$3:$B$12,0))

I would appreciate if anyone can give me some guidance. ​

Data range A2:G12 and Desired result range I2:M5

[TABLE="width: 719"]
<colgroup><col><col><col><col span="4"><col><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 7"]DATA[/TD]
[TD][/TD]
[TD="colspan: 5"]DESIRED RESULT[/TD]
[/TR]
[TR]
[TD]TEXT[/TD]
[TD]HERO[/TD]
[TD]TEXT[/TD]
[TD]TEXT1[/TD]
[TD]TEXT2[/TD]
[TD]TEXT3[/TD]
[TD]TEXT4[/TD]
[TD][/TD]
[TD]CODE[/TD]
[TD]TEXT1[/TD]
[TD]TEXT2[/TD]
[TD]TEXT3[/TD]
[TD]TEXT4[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]5848[/TD]
[TD]ORDER[/TD]
[TD]US[/TD]
[TD]US[/TD]
[TD]US[/TD]
[TD]US[/TD]
[TD][/TD]
[TD]68798[/TD]
[TD]US[/TD]
[TD]UK[/TD]
[TD]CHINA[/TD]
[TD]NZ[/TD]
[/TR]
[TR]
[TD]CHINA[/TD]
[TD]6847[/TD]
[TD]ORDER[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD]UK[/TD]
[TD][/TD]
[TD]4587[/TD]
[TD]US[/TD]
[TD]UK[/TD]
[TD]CHINA[/TD]
[TD]NZ[/TD]
[/TR]
[TR]
[TD]NZ[/TD]
[TD]68798[/TD]
[TD]ORDER[/TD]
[TD]CHINA[/TD]
[TD]CHINA[/TD]
[TD]CHINA[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD]22235[/TD]
[TD]#ref ![/TD]
[TD]#ref ![/TD]
[TD]#ref ![/TD]
[TD]#ref ![/TD]
[/TR]
[TR]
[TD]AUS[/TD]
[TD]4587[/TD]
[TD]ORDER[/TD]
[TD]NZ[/TD]
[TD]NZ[/TD]
[TD]NZ[/TD]
[TD]NZ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EUR[/TD]
[TD]1112[/TD]
[TD]ORDER[/TD]
[TD]AUS[/TD]
[TD]AUS[/TD]
[TD]AUS[/TD]
[TD]AUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RUS[/TD]
[TD]22235[/TD]
[TD]ORDER[/TD]
[TD]EUR[/TD]
[TD]EUR[/TD]
[TD]EUR[/TD]
[TD]EUR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERA[/TD]
[TD]456[/TD]
[TD]ORDER[/TD]
[TD]RUS[/TD]
[TD]RUS[/TD]
[TD]RUS[/TD]
[TD]RUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NIPA[/TD]
[TD]487[/TD]
[TD]REJ[/TD]
[TD]ERA[/TD]
[TD]ERA[/TD]
[TD]ERA[/TD]
[TD]ERA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BAN[/TD]
[TD]587[/TD]
[TD]PENDING[/TD]
[TD]NIPA[/TD]
[TD]NIPA[/TD]
[TD]NIPA[/TD]
[TD]NIPA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]657[/TD]
[TD]ORDER[/TD]
[TD]BAN[/TD]
[TD]BAN[/TD]
[TD]BAN[/TD]
[TD]BAN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

D3:G12 is 9 rows by 4 columns.

My guess is that MATCH($I3,$B$3:$B$12,0) is returning a value greater than 4.

 

[markmzz]

Hi!

The correct formula is:

=INDEX($D$3:$G$12,MATCH($I3,$B$3:$B$12,0),MATCH(J$2,$D$2:$G$2,0))

Markmzz

 

[Marcelo Branco]

You reversed the parameters - it should be INDEX(range,row_num,column_num), not INDEX(range,column_num, row_num)

Try
=INDEX($D$3:$G$12,MATCH($I3,$B$3:$B$12,0),MATCH(J$2,$D$2:$G$2,0))

M.