This is the look up function being used.
=VLOOKUP(A1070,[Test_ShippedPO2.xls]Sheet1!$A:$C,3,0)

for example item number 6901-3 is listed in both worksheets and has a qty to
return. However it returns #na. The qty to return has been formatted to
general, text and number, as has the item number. none of the different
formats work. I also have the same problem with numbers who do not have a
dash. Can I email you the spreadsheets to take a look.
--
Tickfarmer


"Alojz" wrote:

Hi Sheeloo, u r correct dash definitily does not create problem in vlookup.

"Sheeloo" wrote:

Problem is not with dash.

It must be the format or extra spaces

Test both with ISTEXT..
Check LEN

Remove all formats from the lookup cell
Copy the value from the cell in VLOOKUP there and see

I pasted 10050-4 in A1 and H1 and entered TEST in I1
Following formula in B1 returned TEST as expected
=VLOOKUP(A1,H:I,2,FALSE)


"Tickfarmer" wrote:

Working with 2 spreadsheets - trying to look up by value A1 (Item number).
Many of the rows work correctly...however for item numbers that contain a
dash (-) (Example: 10050-4) the result is #NA. I have verified that the item
number is on both worksheets.

I have tried converting the item to text and numeric, but all entries that
include a dash - return #NA.

Please help.

--
Tickfarmer