Index Match not Formatting Correctly

**CreamOfWheat** · 05-20-2015, 07:30 PM

Can anyone help me figure out why A11 is not Matching to J4110 (highlighted them red) I figure it's a formatting issue but both are formatted the same?

**daffodil11** · 05-20-2015, 07:44 PM

One is a number and the other is text.

"1" is not the same as 1

If your formula converts both range and lookup into the same format, it'll work no matter how their formats are mismatched in your data.

=INDEX(K:K*1,MATCH(B11*1,J:J,0))

or

=INDEX(value(K:K),MATCH(VALUE(B11),J:J,0))

**But only if the number is less than 15 digits long

**jhren** · 05-20-2015, 07:53 PM

They are not formatted the same for one. The other is that A11 is a text entry even though it appears as a number. J4110 is an actual number entry.

To convert A11 and other column A cells to number entry, select, then click the ! in diamond icon beside first selects cell and choose Convert to Number from local menu.