VLOOKUP Help

[Racingzeus]

I have a spreadsheet with over 12000 enties in it on sheet 1 named Details.
There are 3 columns with data, col A PART NUMBER, col B PART DISCRIPTION and
col C PRICE. I have added in a second sheet that I wish to use that if I put
in the Part number in col A it will return the price in col B. In col B i
have used the following =VLOOKUP(A1,Details!A1:C12668,3,FALSE)
Using this formula whatever part number I enter in A1 I receive N/A in B1
even though that pert number exists. If I change the formula to
=VLOOKUP(A2,Details!A1:C12668,3,TRUE) I receive a price but it is incorrect.
I have sorted sheet 1 Details into ascending order.
Please help me with this.

Many Thanks
Dave

 

[Farhad]

Well the thing i guess is that it may be the format of the partnumber in the
detail sheet is deffrent to the partnumber that you enter in the summary
sheet so if you just want to find one by one why you don't use Find feature
that Excel has?

Thanks,

 

[Racingzeus]

Hi, The format for Part Number on both sheets are set to number and on the
summary sheet I am entering lots of part numbers and then auto sum to give me
an incoice total so the find feature would not be of any use.

Dave

 

[L. Howard Kittle]

Hi Dave,

It is possible the part numbers look alike but one may have a leading or
trailing space. In an empty cell enter =A1=Details!A1 to test for True or
False. Where the A1 cells of each sheet are a pair of troublesome lookups.
You will get a True or False, and I suspect yours will return False. If so
adjust as needed to clean up the part numbers of the troubled sheet.

Also, you may want to try this with 'clean' part numbers on both sheets.

Select two cells in a row where you want your lookup values to appear, (the
description and the price). While still selected type in this formula:

=VLOOKUP(D1,A1:C5,{2,3},0)

Now hit Ctrl + Shift + Enter, you should get { } brackets around the
formulas and you description will be in first cell and the price in the
second. Of course adjust the cell address's to suit your sheet. And your
list does not need to be sorted when using the FALSE or 0 as the fourth
argument.

HTH
Regards,
Howard

 

[Racingzeus]

Hi Howard
When I enter =A1=Details!A1 I result with False. I have looked at the part
number and dont see any leading or trailing spaces etc. I dont fully
understand what you mean by "Where the A1 cells of each sheet are a pair of
troublesome lookups.
You will get a True or False, and I suspect yours will return False. If so
adjust as needed to clean up the part numbers of the troubled sheet."
I am new to working with Excel formulas so please excuse my lack of
knowledge in this
What exactly does =A1=Details!A1 do and why does it return False ?
Also if there are any spaces or unseen characters in my part numbers is
there any way to remove these as to edit over 12000 lines would be a task
as the part numbers where imported into Excel from an Access Database.
Regards Dave

 

[Racingzeus]

Howard,
I have took another look at the Part Numbers and there is a trailing space
at the end of every number. Is there any way I can remove thes ?

Dave

 

[L. Howard Kittle]

I'll get right back with you in about 1 hour. Need to go

Regards,
Howard

 

[L. Howard Kittle]

Okay, the NA from your formula may be because the lookup value and the table
lookup are not identical.

Try this: in a blank sheet in A1 enter a 1 and then enter a 'space" and in
A2 enter 1. Now in A3 enter =A1=A2. It will return FALSE... the values look
the same but they are not. This is an example of the =?=?

To remove unwanted spaces you can try =Trim(A1) and pull down. Gets rid of
leading and trailing spaces and retains internal spaces.

I would be glad to look at a sample workbook and try to devise a solution.
If you send one, provide all the detail of what you want to happen and in
what cells you want it to happen, etc.

(e-mail address removed)

Regards,
Howard