VLOOKUP & Data Validations Tool errors

[William DeLeo]

Greetings (sorry so long),

I am observing some odd behavior in a cell with data validatio
configured as well as with the results of cells using VLOOKUP. Bot
look at the same list/range so I question if the issues are related.
The list consists of numbers like 20030528.01, 20030528.02, 20030528.0
(date plus index).

Data Validation cell: It is configured to use the contents of a lis
as acceptable entries and has the drop down list option activated. I
I type in any values with index values not equal to ".01" (i.e. .02
.03, etc), I get an error saying the value is not in list. But, th
same value can be pulled from the drop down list w/o a problem.

VLOOKUP cells: the cells have formulas like the following
=VLOOKUP($B$4,'Mixture Archive'!$A:$W,2,FALSE). Column A of this rang
is the list called upon for the data validation cell. Sometimes th
functions returns the correct results, other times #N/A. At all time
the list is sorted ascending as required for an exact match (if
understand correctly). The value being searched for does not chang
location nor in any other way (that I can tell). If I change it t
TRUE, no errors are produced but the return is not always correct.

The connection: The value entered into the data validation cell get
writen to the source cell for the VLOOKUP. If the data validation cel
has a problem with it (i.e. if I type it in), then the results of th
VLOOKUP also produce an error. If I use the DDL to enter the value
the VLOOKUP works correctly.

Can anyone see an error in my logic or how I am using these functions?
Everything seems right to me and I am at a total loss.

Thanks so much for taking the time to read all this

 

[Frank Kabel]

Hi
sounds like some formating errors:
- first you don't need to sort your lookup range as you use 'False' as
last parameter
- Check if both values 8lookup range and search range) are either both
formated as 'Text' or as 'Number
- for your list: Could be the values in your list are stored as text
and you enter a number (or vice versa)

 

[William DeLeo]

That was my thought as well but I can't determine if that is the case.
When I pick a cell and go to "format - cells ...", all say "number".
How else can I check if the values are actually numbers or if they ar
strings? Is there a way to force this?

Thanks so much Frank

 

[Frank Kabel]

Hi
use the formula
=ISNUMBER(cell_reference)
The format dialog does not help

 

[William DeLeo]

I forced everything to be strings and it is working fine now ... thanks