vlookup problem

[hisdwelling]

I have a list of part #s, prices, etc. in sheet 1, and part #s and descriptions on sheet 2. I need to bring the description from the sheet 2 into the corresponding row Column C on sheet 1. I have tried numerous vlookup formulas, but they all return #n/a as if the part # does not exist on the 2nd sheet, when in fact it does.
The formula I think is right is:
=VLOOKUP(A2,Sheet2!$A$2:$B$4210,2)
where A2 = the part # I need the description for, and Sheet2 column a contains a list of part #s, and column B contains the descriptions.

I'm thinking that the problem has to do with cell formatting, but I have tried everything I know to assure that column A on each sheet share the same formatting.

Can anyone help?

 

[Brian from Maui]

Did you sort your columns?

 

[Steve Hartman]

Try =VLOOKUP(A2,Sheet2!$A$2:$B$4210,2, FALSE)

 

[hisdwelling]

Yes, however I have both 4 digit and 5 digit part #s, so the 4 digit #s don't sort to the top.

 

[Mark W.]

Make sure that the data type of your lookup_value is the same as the values in the left-most column of your table_array. These values must match in every respect... no trailing spaces allowed.

 

[hisdwelling]

Steve,

I've tried that too... same result.

 

[hisdwelling]

Steve,
I've tried that too... same result.

 

[Mark W.]

On 2002-04-26 13:11, hisdwelling wrote:
Yes, however I have both 4 digit and 5 digit part #s, so the 4 digit #s don't sort to the top.

Hint, hint... that's because your numbers are really text (e.g., "1234"). Guess what? "11111" will sort ahead of "1234.

 

[Steve Hartman]

=VLOOKUP(A2,Sheet2!$A$2:$B$4210,2) should be=VLOOKUP(A2,'Sheet2'!$A$2:$B$4210,2)

 

[hisdwelling]

I ran the =trim function on all of the unit# cells. I know there aren't any leading or trailing spaces.