VLOOKUP not working with data from PeopleSoft

[Celia]

I have data which has come from PeopleSoft. One file is a list of current
employees (about 4,500 rows) including a unique ID and I am looking up their
date of birth in another file which is list of all employees, past and
present (just over 7,000 rows). For some reason the vlookup is not returning
any data and is just showing the formula in the cell. I feel the problem is
something in the format of the data coming from PeopleSoft but can't think of
anything else to try. I use vlookup heaps so don't think there is anything
wrong with the formula.

I have checked that the employee IDs are numbers, not text and that there
are no leading or trailing spaces.
I have checked that the DOB is a date format.
I tried copying the data from the all employees file into a sheet in the
current employees file and doing the lookup from there.
I tried naming the lookup range.
I tried sorting by ID even though I don't need to as it's exact match.

Data in the files looks something like:

ID Name DOB
10001 Jones,Fred
10005 Smith,John

and in the "all employees" list, the DOB uses the first date format
(*dd/mm/yyyy).

Formula reads: =VLOOKUP(A2,Sheet4!$A$1:$B$7291,2,false)

Any help will be greatly appreciated!

 

[Fred Smith]

If your problem is it "is just showing the formula in the cell", then your
cell is formatted as text.

Format it as General, and re-enter your formula.

It should work then.

Regards,
Fred

 

[Celia]

Fred, many thanks - that worked fine. I think I might have tried
reformatting the cell previously (I tried so many things I can't remember
them all !!) but the trick (as you mention) is to re-enter the formula
because it doesn't "fix" automatically with the reformatting.

Have a great day.

 

[T. Valko]

Tip...

Re-enter doesn't necessarily mean retype.

Change the format to General then, with the cell still selected, either
double click in the cell then hit Enter or, hit function key F2 then hit
Enter.