Exact() Countif() and Vlookup() inconsistency

[Timmy Mac1]

With regard to a previous posting of mine, and with finally the time t
look at it again I have noticed the following when applying a cel
input of 123456 with a cell that is the result of a concatenate of 12
and 456...

Although they return different values for ISTEXT and ISNUMBER
nevertheless EXACT(cell1,cell2) will produce a TRUE result an
COUNTIF(cell1,cell2) will produce a result of 1.

However Vlookup(Cell2,cell1,1,0) produces #N/A.

Is this a well known "feature" of Excel? Obviously the solution is t
ensure the data is either all text or all values beforehand which, wit
hindsight, I will now do, but it did cause me some consternation!

 

[Frank Kabel]

Hi
I couldn't reproduce this behaviour. If you like email me an example
file with these data and formulas

 

[Aladin Akyurek]

Your observations are right:

Both CountIf and Exact will coerce text-formatted numbers into true numbers.
Vlookup, on the other hand, does not coerce. Hence, for Vllokup, 123456 and
the result of ="123"&"456" are different things.