When is a number not a number? Match() does not recognize number.

[bw314]

I found a workaround by converting to text but would like to know what cold be causing this.
=====================================
Match(1.2,ARRAY,0) cannot find the row with number 1.2

I imported data from a CSV file. One column is group number and it's sorted on that column. I created a column that added 0.1 to each subsequent row with the same group number to get a column like this:

GroupMembers
1.1
1.2
2.1
2.2
2.3
2.4
3.1
3.2

When I use the formula =MATCH(1.2,GroupMembers,0) on this calculated column, the result is #N/A

When I manually overwrite (type) the data, then the match formula works.

The values in the column are verified to be numbers using =ISNUMBER(F5) with the result TRUE.

The column is formulated as a number. If I increase the decimal places, the display changes appropriately.

When I perform a mathematical operation to multiply by 1, add 0, or multiply by 10, I get the appropriate numerical results., so it behaves as a number for everything except the match formula.

When I typeover the calculated cell with the same number, voila, the Match formula now magically works!

My solution is to use the TEXT function to create a new text column and in the Match formula so that text finds text. =MATCH(TEXT(1.2,"0.0"),$R$5:$R$19,0)

But what is going on? When does a value act and display like a number but somehow isn't a number? If there was a space or magical invisible character, then mathematical formulas and changing the number of decimals displayed wouldn't work, right?

When I use the =MATCH(TEXT(1.2,"0.0"),$R$5:$R$19,0) formula on the original column, I get the #N/A value, so it's not recognized as matching text either. Currently the list is sorted although it doesn't need to be for an exact match.

A mystery.

 

[SanjayGMusafir]

To convert a text to a number, there can be many ways

Then copy the column and 'paste special' using multiply function

Text shall be converted to number

Check this - Convert numbers stored as text to numbers - Microsoft Support.

or watch this -

 

[bw314]

Thanks, but it's not text and not a number.

On the original calculated column, all of these formulas fail.
=MATCH(1.2,$R$5:$R$19,0)
=MATCH("1.2",$R$5:$R$19,0)
=MATCH(TEXT(1.2,"0.0"),$R$5:$R$19,0)

Interesting:
When I enter '1.2 in a cell:
- Left position
- I get the "number stored as text" flag
- Changing the formatting to a number with more decimal places FAILS to change the display (as expected)
- Mathematical formulas work fine and the result is a number.
- Isnumber correctly says "FALSE"

This behavior is different than my column, which is:
- right position
- no flag
- Changing the formatting to a number with more decimal places DOES correctly change the display (as expected)
- Mathematical formulas work fine and the result is a number.
- Isnumber says "TRUE"
- It behaves as a number in every way that I have tested, except for the "Match" formula

Using the latest excel for desktop via office365.

Still a mystery to me. (but not critical because my workaround is to change it to text)

 

[SanjayGMusafir]

@bw314 try the solution I gave in #2 and revert if it works or not

 

[Alex Blakenburg]

My solution is to use the TEXT function to create a new text column and in the Match formula so that text finds text. =MATCH(TEXT(1.2,"0.0"),$R$5:$R$19,0)

When I use the =MATCH(TEXT(1.2,"0.0"),$R$5:$R$19,0) formula on the original column, I get the #N/A value, so it's not recognized as matching text either.

The above 2 seem to say opposite things, does converting to text work or not ?

Please update your profile to show what version of Excel version you are using, the solution often varies based on the version.
You are correct in that IsNumber and checking that the format changes when the number format changes are methods to check whether Excel is recognising it as a number.
Multiplication is not really a valid test since it will convert the string on the fly although you are correct in that if there was an unusual invisible character it would error out.

The conversion steps sound like they would work but don't explain why it is happening.
Maybe see if this evaluates as TRUE (assuming isnumber is still false)

=ROUND(F5,1)=F5

If that looks promising and you have MS365 you can then try:

=MATCH(1.2,ROUND($R$5:$R$19,1),0)

 

[bw314]

The above 2 seem to say opposite things, does converting to text work or not ?

Please update your profile to show what version of Excel version you are using, the solution often varies based on the version.
You are correct in that IsNumber and checking that the format changes when the number format changes are methods to check whether Excel is recognising it as a number.
Multiplication is not really a valid test since it will convert the string on the fly although you are correct in that if there was an unusual invisible character it would error out.

The conversion steps sound like they would work but don't explain why it is happening.
Maybe see if this evaluates as TRUE (assuming isnumber is still false)

=ROUND(F5,1)=F5

If that looks promising and you have MS365 you can then try:

=MATCH(1.2,ROUND($R$5:$R$19,1),0)

Note: I don't have a current problem. I was able to workaround the behavior but I thought it strange and wanted to understand it because best as I could tell the data acted as a number for everything except this match function.
--
The two statements are different. The first refers to a new text column, the second is on the original column. The point being that the formula that work on the data converted to text doesn't work on the original column, so the original column is not text.

The next time that I import the csv file, I'll specify that column as numerical.

Cheers.

 

[Alex Blakenburg]

If the file extension is csv you won't get an opportunity to specify a field type. Are you using Power Query to import the data ?
Also how are you doing this ? If you are using "&" or concatenate you are converting it to text at that point.

created a column that added 0.1 to each subsequent row with the same group number to get a column like this:

You have still not updated your profile so I don't know if MS 365 functions will work for you.

 

[Tupe77]

My suggestion is not based on precise knowledge of how Excel engine works, so this is only one possible explanation:
Can your number be a floating point number and its presentation format matches, but the content differs?
A floating-point number is an approximate representation of a real number and comparing it programmatically is very slow and its use should be avoided in all comparisons if possible.