ISNUMBER(MATCH)) doesn't work.

Hello All,

I receive a sheet that has a list of values in column J. I am the owner of a sheet which has some, but not all of those values. All of the values are numerical. What I want to do is ensure that the value in my spreadsheet is in the spreadsheet I have received.

I used the following in Cell B2...

IF(ISNUMBER(MATCH(A2,'[Workbook]Sheet1!'J:J,0)),A2,"Error") - We will call this formula (X)

I know that all my numbers should be in there but it is not working. It only returns, "Error". I had a look around online and it suggested some checks should be performed. =A2=J4506 (This returned TRUE)
I also tries ISTEXT on both the cells and they both returned TRUE even if they were values.

Something in performing these manual steps, triggered (X) and it is now working. Any modifications I make in either sheet correctly updates the formula but it does not allow me to copy down. It goes back to recording an error it seems that when I manually link the cells, it can function. If I change A2 from 156339 to 156340, it works. If I change the other workbook from 156339 to 156340, it works. If I change A2 to A3, it doesn't.

Can anyone help?

L

Hello,

I am fairly sure that it is something to do with the way that Excel treats numbers as text and text as numbers.

I have been able to duplicate the problem by setting both Input01 and Input02 as cells containing text and then copying and pasting the values again.
For Input 01, I set the cells as text and then copied into Notepad and back again. For Input 02 I set the cells to text and copied and pasted the values, *without* copying to notepad first.
As you can see, I get Errors in the output.

My problem is that for the real data, I am not in control of the formatting and I wonder if there is a way to force it.

Thanks

L

Perhaps as countif is not number/text sensitive

=if(COUNTIF('[Input02.xlsx]Input 02'!$A:$A,'[Input01.xlsx]Input 01'!$A$2),'[Input01.xlsx]Input 01'!$A$2,"Error")
or
=if(COUNTIF('[Input02.xlsx]Input 02'!$A:$A,'[Input01.xlsx]Input 01'!$A$2),'[Input01.xlsx]Input 01'!$A$2*1,"Error") to make them all numbers