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

DATA

DESIRED RESULT

TEXT

HERO

TEXT

TEXT1

TEXT2

TEXT3

TEXT4

CODE

TEXT1

TEXT2

TEXT3

TEXT4

UK

5848

ORDER

US

US

US

US

68798

US

UK

CHINA

NZ

CHINA

6847

ORDER

UK

UK

UK

UK

4587

US

UK

CHINA

NZ

NZ

68798

ORDER

CHINA

CHINA

CHINA

CHINA

22235

#ref !

#ref !

#ref !

#ref !

AUS

4587

ORDER

NZ

NZ

NZ

NZ

EUR

1112

ORDER

AUS

AUS

AUS

AUS

RUS

22235

ORDER

EUR

EUR

EUR

EUR

ERA

456

ORDER

RUS

RUS

RUS

RUS

NIPA

487

REJ

ERA

ERA

ERA

ERA

BAN

587

PENDING

NIPA

NIPA

NIPA

NIPA

NA

657

ORDER

BAN

BAN

BAN

BAN

<colgroup><col><col><col><col span="4"><col><col span="5"></colgroup><tbody>
</tbody>

 

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