Mid Function nested in Index return #N/A

1000001 10 01000003 #n/a =index($b$1:$b$20,match(mid(d1,2,7),$a$1:$a$20,0),1)
1000002 20 01000006 #n/a
1000003 30 01000011 #n/a
1000004 40 01000015 #n/a
1000005 50 01000019 #n/a
1000006 60
1000007 70
1000008 80
1000009 90
1000010 100
1000011 110
1000012 120
1000013 130
1000014 140
1000015 150
1000016 160
1000017 170
1000018 180
1000019 190
1000020 200

Hi.

Try coercing the string to a numeric first:

=INDEX($B$1:$B$20,MATCH(0+MID(D1,2,7),$A$1:$A$20,0),1)

Note that this will only work in all cases if all of your entries in column A are themselves numeric.

Regards

It's not the INDEX function. It's MATCH, which will not consider a number and the equivalent number formatted as text as identical.

If all of your entries are text, or all are numeric, then there's no issue. The problem arises when you are comparing a text value against a numeric one, as in your case.

Regards