Index Match of variable cell returns #n/a, but manual typing gives correct answer

[thesomersetninja]

I’m trying to create a complete fixture list for next year’s Rugby World Cup. It’s going all right but I’ve hit a problem with the group tables. I’ve managed to get the teams to adjust their position based on 1) points for winning and then 2) points difference. Whatever I do though, I cannot get an index match of my variable ranked cell to return anything but #n/a. If I type the same answer into the lookup cell it works; getting it to do automatically is doing my head in…

=INDEX($A$1:$A$5,MATCH(H2,$B$1:$B$5,0))

where $A$1:$A$5 = teams
H2 = ranking position (which will vary – formula below)
$B$1:$B$5 = 1^st, 2^nd, 3^rd etc

If I try and index match

=TRIM(RANK(Standings!N3, Standings!$N$3:$N$7,0)+COUNTIF(Standings!$N$3:N3,Standings!N3)-1)

it won’t work, even though =TRIM(RANK(… results in the correct answer.
Any help as to why the index match of the dynamic cell doesn’t return what I what, but manual entry does? I really need the table to adjust automatically otherwise there's little point in proceeding.

Thanks

 

[sheetspread]

Was this data imported? One range might be stored as text which will return #N/A when trying to match a number. Use the Value() or Text() functions to align. You might also try ctrl-h replace = with =.

 

[thesomersetninja]

Was this data imported? One range might be stored as text which will return #N/A when trying to match a number. Use the Value() or Text() functions to align. You might also try ctrl-h replace = with =.

No - the data isn't imported. The most I'm doing is moving it around sheets in the same workbook. Doing the replacement hasn't made any difference, unfortunately

 

[sheetspread]

You tested with isnumber() and/or istext() to see that the data types are identical?

 

[thesomersetninja]

Yes, all are in the same format (number, except obviously for the teams)

 

[sheetspread]

Why do you need the trim function? Does

=TRIM(RANK(Standings!N3, Standings!$N$3:$N$7,0)+COUNTIF(Standings!$N$3:N3,Standings!N3)-1)+0

do anything?

 

[thesomersetninja]

The trim was there as a belt-and-braces measure. But the addition of the +0 has done the trick. Star man!

Appreciate the help on this