Problem with VLookup

[Bob1866]

Hi, If I type a value into the cell which is the reference for the Vlookup i
get the error "#N/A" yet if i copy and paste the same number from the lookup
column into the reference cell it works perfectly, I am guessing that somehow
there is a formatting issue with the data that comes from the database
(Access) and Excel I am just not sure what it is, I have pasted the data into
Notepad to see if there is anything added in the formatting but it is still
exactly the same as I typed. But if I then try and copy that back into the
reference cell I get the same error. Just wondering if anybody has got any
ideas how to fix this annoying problem.

Many Thanks

Windows Xp pro
Office 2003

 

[Peo Sjoblom]

The data from Access is probably text whereas the data you type into the
cell is
a number, copy an empty cell formatted as general or number and select all
the imported numbers, then do paste special and select add. That will force
any numbers formatted as text to turn into matching numbers.



--


Regards,


Peo Sjoblom

 

[Pete_UK]

You may have values in your lookup table which look like numbers but
which are actually text values, and the lookup item is a proper number
(or vice-versa). Ways around this are:

=VLOOKUP(A1&"",table,column,0)

and:

=VLOOKUP(A1*1,table,column,0)

The first one converts the lookup value to text if you have text
values in your table, whereas the second one converts it to numbers if
that's what you have in your table. You can make this more universal
like this:

=IF(ISNA(VLOOKUP(A1&"",table,column,0)),IF(ISNA(VLOOKUP
(A1*1,table,column,0)),"not present",VLOOKUP(A1*1,table,column,
0)),VLOOKUP(A1&"",table,column,0))

You will need to substitute your table range and a suitable number for
column, and I have assumed that you are trying to match on a lookup
value in A1.

Hope this helps.

Pete

 

[Gord Dibben]

Sounds like the data coming from Access contains maybe extra space(s)

In a helper cell enter =LEN(cellref) for one of the cells in your table.

Same length as what you type into the reference cell?

Or perhaps the values from Access look like numbers but are actually text?

Format all to General.

Copy an empty cell.

Select the table range and paste special>add>ok>esc to coerce the cells to
become numbers.


Gord Dibben MS Excel MVP

 

[B. R.Ramachandran]

Hi,

I think that the cell that contains the reference is formatted as text.
Change it to number.

Or, modify the VLOOKUP formula as follows:
=VLOOKUP(A1*1,lookuprange, ) where A1 is the reference cell. The
"A1*1" in the formula will treat the text in A1 as a number.

Please click "Yes" if the solves your problem.

Regards,
B. R. Ramachandran

 

[Bob1866]

Hi, Many thanks for your reply, I have tried your suggestion but to no avail,
the only difference was that the vlookup no longer worked if I copied and
pasted the text into the reference cell from the lookup column. I have
already set the formatting to Number, I have also tried a custom format
aswell but neither seem to make any difference.

Below is the Vlookup code I am using

=VLOOKUP(AA9,AB7:AG9999,1,FALSE)

And this is how I interpreted your solution

=VLOOKUP(AA9*1,AB7:AG9999,1,FALSE)

If I have got this wrong please let me know.

Many Thanks.

Windows Xp pro
Office 2003

 

[Bob1866]

Hi, Many thanks for your help, I have now managed to fix the problem, the
issue was caused because the database format was set as text and not number,
but it works fine now.

Thanks