Match returns NA error

[YasserKhalil]

Hello everyone
I am using the Match function so as to return a matching value (exact match)
The formula works for some IDs but not for others. As for the others return NA
This is the formula

Code: Select all

=MATCH(C3,'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0)

And when pressing F9 the matching value is there ..but the formula return NA error

Untitled.png

[rory]

You're matching a number against text. You'd need to match ""&C3 rather than just C3.

[YasserKhalil]

That's fine and works as you reply
But my question is why some of those numbers are returned with a result while others returns error .. although all the numbers in the source file and the target file formatted the same (General) .. and even I tried to format to Text and to Number

Untitled.png

[rory]

Look at the data in your evaluation picture. Some of the numbers have quotes round (which means they are actually text in the source data) and others don't. So clearly your source file is not consistent.

[YasserKhalil]

Thanks a lot for the reply that helped me a lot
I have used Clean to clean the source and now the formula works well
Best Regards

[Doc.AElstein]

Just an alternative… you may have realised already…. that you could do something of this sort of form instead of cleaning your data..
=IF(ISERROR(MATCH(C3,'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0)), MATCH("" & C3 & "",'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0), MATCH(C3,'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0))

What that is doing is that if an error is got looking for a Match with the number as a number, then it will look for a Match with that number as a text

To be on the safe side, you might want to make sure that the “number” looked for is a number in the first place.
So I think then you could do something like C3*1 to make sure that it is a number in the first place

=IF(ISERROR(MATCH(1*C3,'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0)), MATCH("" & C3 & "",'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0), MATCH(1*C3,'[MOE EMIS Download Tool teesss.xlsm]INF'!$K1:$K40,0))

I think that will probably take care of all scenarios: C3 can be text or a number , and the element that it should match against in the {array} can be a text or a number

Alan

( That formula form can be simplified a bit for XL 2007+ , but that formula form above will also work for XL 2003 )

[YasserKhalil]

Thanks a lot Mr. Alan
But in my opinion I see cleaning the data is better so as to avoid any troubles later ..

[Doc.AElstein]

I personally would do both : clean the data and put the things in to make it work with dirty data.
That would be a very unscientific and unprofessional approach, But I am not a ( Computer ) Scientist or computer professional so I Let myself do insensible and unscientific things :-)

My approach is what we say/ call in English as .. the ..
....." Belt and Braces ” ..... approach.
https://dictionary.cambridge.org/de/wor ... and-braces" onclick="window.open(this.href);return false;
https://idioms.thefreedictionary.com/belt+and+braces" onclick="window.open(this.href);return false;
https://en.wiktionary.org/wiki/belt_and_braces" onclick="window.open(this.href);return false;
.....

[YasserKhalil]

Thanks a lot for your advice Mr. Alan