VLookup not working

F

foolio

Can anyone come up with a reason why a vlookup would not work ??

I have part numbers and sales category on one workbook.

I have part numbers and prices on another workbook.

I am just trying to pull over the sales category from the one workboo
onto the one with the part number and prices.

Now if it hadn't worked at all i would say its a mistake of mine...
but it worked for over half the part numbers..... and yes the ones tha
didnt work are on both of the workbooks

??????? ARG ???????
 
A

AlfD

Hi!

Can you post the formula you are using and give some info on the rang
of data in your lookup table and in the cell being used for lookin
up?

Al
 
R

Rollin_Again

Since some of the lookups are working, I would make sure that the par
numbers on both workbooks do not have any leading or trailing spaces o
them. Look in the formula textbox to see if there are an extra space
in your cells and delete them if you find them. Also as a extr
precation, I would reformat the cells in in both workbooks to mak
positively sure they have the exact same format (i.e - Text, Number
etc)



Rolli
 
F

foolio

How the heck would I do a vlookup without a formula ???

=VLOOKUP(A1,'[STARRETT 0604l.xls]Starrett 2004'!$B$2:$I$8794,8,FALSE)

Value I want is in the 8th column of the table....And yes I hav
checked for trailing spaces and format.... no extra spaces, bot
formatted in general

For more info on the workbook....

book1,
a) contains part number
b) contains description
c) contains formula

STARRET 0604,
a) contains EDP number (not needed)
b) catalog number (part number)
c) description
d) discount code
e) list
f) pkg quantity
g) discount
h) net
i) sales category (value i want
 
D

Don Guillett

my excel 2002 automatically puts in the path when I close the source file.
=VLOOKUP(a1,'C:\yourfolder\[yourfile.xls]yourworksheet'!$A$7:$D$43,4,0)
A good way to check your formula
1. in the destination workbook put =vlookup(a1,
2. now open the source and highlight the extraction range
3. touch the enter key
4. ignore the message
5. complete the formula
6. see if you have the correct answer.
 
S

Sean

Try to take your destination column and select data/text
to columns. Select delimit for tab, semi colon, comma and
space. If it asks to replace cells, select no, insert some
columns after your data column as this process will
overwrite your adjacent columns with the new delimited
fields. Once you have done that re-do the text to columns
field. If this doesn't work try the process on your lookup
column too.

Good Luck.
 
A

AlfD

Hi!

I still don't know how "not working" manifests itself. Blank? Erro
message? Wrong answer?

Al
 
F

foolio

Don, I just select the path as well....its not like i type it in, i
puts in the path by itself.

Alf, sorry for the confusion, the error is that it acts as if it doe
not find the part number, just brings back a blank cell.

Sean, dont have time to do that now, ill try it tomorrow.

:) :) :)

Thanks for the replie
 
M

Mike Chadwick

Hope this isnt too silly, but you have checked the data
you are searching in is sorted alphabeticaly? Non sorted
data will give you some results, but not all.
 
N

Nick Hodge

Can you post the formula first.

There are normally two reasons for this.

1) The lookup range is left at relative A1:A100 rather than $A$1:$A$100
which would mean the lookup range eventually 'creeping' off the bottom

2) Some of the data looks the same (i.e it *should* lookup) but does in fact
have hidden spaces or similar. (To overcome this try copying and pasting a
lookup value to the lookup range or use the TRIM or CLEAN functions on both
ranges)


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top