VLOOKUP Issue - Not resolving data

[dsniff]

Hi guys,

This has been driving me crazy and I am currently working on a censored version I can post here but let me explain the issue.

I have 3 worksheets

- Timesheets
- Temps
- Data

All the cells in the timesheets worksheet with user inputted data in have formulas pointing to the data spreadsheet. So basically I import my data from my database into the data worksheet and timesheet worksheet picks it all up from there. Simple.

The temps timesheet is a huge list of worker details, these worker details match the details in the data sheet; so what happens is:

- Input data in data sheet
- Timesheets worksheet picks up the reference number from the data sheet
- Does a vlookup on the temp sheet to match the right record

Ok so it was all working 100% fine until I started using my real data. The ref number in the data sheet appears in the box on the timesheets sheet but the vlookup doesn't work!

The weirdest thing about it is that if I go to the temps sheet, copy a list of reference numbers and paste them in on the data sheet it works!!! although the numbers are exactly the same!!!!!!!

This have probably confused you but has anybody experienced the same problem? At least until I upload a sample workbook.

Thanks in advance.

Dan

[romperstomper]

It sounds to me as though one set of numbers are stored as text and the other as numbers. If you remove any manual alignment, does one set of numbers appear left aligned in the column and the other right-aligned?

[dsniff]

Ah yes, I couldn't tell because the temp sheet is locked but if I copy the numbers over to the data sheet, the numbers that work right align and my imported data left aligns. I think it must be the data type in my access database!

[romperstomper]

If you are looking up the real numbers against the text, then use:
=VLOOKUP(Text(number_cell, "0"),....)
or if vice versa:
=VLOOKUP(--number_cell,....)

[dsniff]

Great! that was the problem, how I feel silly now. Glad it's sorted now cheers mate!