Data type error when using VLOOKUP

I am using VLOOKUP to pull a rate from one workbook to another, but there are two strange things going on:

1. If the lookup value is not in the array, it inserts a number from the table anyway instead of a #REF! error.

2. For 5 specific numbers in the array, if the lookup value is one of them I get a #VALUE! error saying that a value used in the fornula is of the wrong datatype.

Alll columns are formatted as numbers with 3 decimal places.

Has this happened to anyone else?

What formula are you using?

If you only want an exact match make sure your VLOOKUP uses a 4th argument of zero or FALSE. If lookup value is not in the lookup range this will then give you #N/A.

You wouldn't usually get a #VALUE error from a VLOOKUP unless #VALUE occurs in the referenced ranges. If you have a data mismatch check that all data is numeric. You can't do this by looking at the formatting - use ISNUMBER function to check.

If any data is not numeric but looks like it is then you can convert by using the following process.

Copy an unused blank cell.

Select range to convert > Edit > Paste Special > Add > OK

Strangely,

by changing the lookup value from 12.000 to #12 for example, the problem was solved.