Convert Number to Text for Index/Match

[mark.wolven]

I have a column of data and it is a 4 character string, with all charactersbeing numbers. The data ranges from 0004 to 8862. My data in the match column is a mix of text (the values with their leading zeros retained) and numbers - which is some of the rest of the numbers. The problem is that I cannot get all of the data in the column to be read as text; for example, 1000-2500 are numbers, but starting at 2502, they are text.

When doing an index/match, Excel treats 2210 the text value different from 2210 the number. How do I get them sync'd up so that my index/match works?

 

[isabelle]

hi,

i supposed that the data is placed in range A1:B5 and the value to
search in range F1
this is an array formula, you must validate with "ctrl + shift + enter"

=IF(ISERROR(MATCH(F1,$A$1:$A$5,0)),INDEX($B$1:$B$5,MATCH(F1,($A$1:$A$5)*1,0)),INDEX($B$1:$B$5,MATCH(F1,$A$1:$A$5,0)))