I believe that for the VLookup function to work properly both fields that the
lookup is working off of must be the same data type (both text, both #, etc).
Otherwise the VLookup function returns #N/A. Perhaps this is your issue.
Ensure that the data types are the same. The formats could be the same but
the data types can still be different. You can use the TYPE function to
determine the data type of your data.

Hope this helps.

Thanks,
Bill Horton

"sonar" wrote:


Hi,

I have 13 digits in my barcode numbers, and I can only have their cell
format in either TEXT, or NUMBER format.

__________________________________________________ _____________

The thing is, I can't type it in cells that is in a Number format, if
I do, my description column in '13DBC'!$B10 using

=IF(A10<0,(VLOOKUP($A10,'Stock Items'!$A$3:$B$1002,2,FALSE)),"")

gives me a #N/A and does not give me my description.

But it works if I have my cell Format in TEXT format.

__________________________________________________ _____________

The problem now is, is that I want to pass the information from 13DBC
to SHORT-1, with the formula

{=IF(ROWS($1:1)<=COUNT('13DBC'!$V$10:$V$999),INDEX ('13DBC'!A$10:A$999,
SMALL(IF('13DBC'!$V$10:$V$999<"",ROW(INDIRECT("1: "&COUNT('13DBC'!$A$10:$A$999)))),ROW(1:1))),"" )}

but cant, because my cells are in text format in 13DBC column A.

__________________________________________________ _____________

In my 'Stock Item' Sheet the cell format (column A) is Number format,
and I am using this formula

=IF($A455<"",IF(ISERROR(VLOOKUP($A455,Mo!$A$1:$X$ 1000,4,FALSE)),"",VLOOKUP($A455,Mo!$A$1:$X1452,4,F ALSE)))

to extract unit prices, it works for the 3 to 8 digit barcodes
(numbers), but not for the 13 digit barcodes (numbers)

__________________________________________________ _____________

Any idea how I can fix this? Is there a cell format that I can use,
that will not trow the 13 digit numbers in a Scientific format
(2.90688E+12) and that will work with all formulas?. Something I can
use accross the board. e.g my 3 to 8 digits as well.

Regards
Antoinette


--
sonar
------------------------------------------------------------------------
sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424
View this thread: http://www.excelforum.com/showthread...hreadid=399917