COUNTIF Finding Value not there

[Maddogg4Life]

I am running a data validation between 2 systems so I downloaded the data into two different tabs used CONCATENATE to combine location, reference number, item and quantity to create a unique key value. When I run the COUNTIF looking for the key in the target sheet it returns a value of 1 but when I filtered and searched for that value in the target sheet it cannot find it. We changed to a VLOOKUP and got the expected results but still cannot figure out why the COUNTIF failed and why it thinks it found the KEY in the target sheet.

 

[Peter_SSs]

Welcome to the MrExcel board!
To try to work out why the COUNTIF is not doing what is expected, we would need some smallish sample data from the two tabs and an explanation in relation to the sample data.
See XL2BB for providing sample data that we can test with.

 

[Alex Blakenburg]

Is your location numeric by any chance ?
If in your Vlookup you add the 2 lots of double dashes as shown below, does it find it then ?

=VLOOKUP(--C6,--Sheet2!A3:A7,1,FALSE)