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
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
You do not have the required permissions to view the files attached to this post.
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.
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
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 )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
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 :-)