I have two file test1 and book1, in book1, I vlookup in test, for example, 500016433 should get -1, but now it is not, how to change column M in book1, so we can get value from test?



test, book1 is as follows

if I manually put value 500016433 in the vlookup, it is working, so i guess I need to change colmn M format, but how to change, I have around 2000 records, I don't want to change one by one

Attached Images

	test.PNG (78.7 KB, 13 views)
	book1_1.PNG (84.1 KB, 12 views)
	book1_2.PNG (75.2 KB, 12 views)

If I were having your trouble, jolinchew, the first thing I'd check is whether the value is numeric in one workbook and a character string in the other. As I recall, VLOOKUP gets unhappy about that, and claims it couldn't find anything.

While pictures are nice to look at, they are useless for solving most of XL problems - Please post sample sheet(s) - Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

a super easy way to convert a number formatted as text to a number is simply write this formula in an adjacent cell.
=a1+0
Then drag it down copy it and paste the values where you need it.