vlookup problem

hisdwelling · Apr 26th, 2002, 02:04 PM

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 · Apr 26th, 2002, 02:09 PM

Did you sort your columns?

Steve Hartman · Apr 26th, 2002, 02:10 PM

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

hisdwelling · Apr 26th, 2002, 02:11 PM

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. · Apr 26th, 2002, 02:12 PM

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 · Apr 26th, 2002, 02:13 PM

Steve,

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

hisdwelling · Apr 26th, 2002, 02:14 PM

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

Mark W. · Apr 26th, 2002, 02:14 PM

Quote:

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 · Apr 26th, 2002, 02:15 PM

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

hisdwelling · Apr 26th, 2002, 02:17 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 02:04 PM	#1
hisdwelling New Member Join Date: Apr 2002 Posts: 16	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?

Apr 26th, 2002, 02:09 PM	#2
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Did you sort your columns?

Apr 26th, 2002, 02:10 PM	#3
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Try =VLOOKUP(A2,Sheet2!$A$2:$B$4210,2, FALSE)

Apr 26th, 2002, 02:11 PM	#4
hisdwelling New Member Join Date: Apr 2002 Posts: 16	Yes, however I have both 4 digit and 5 digit part #s, so the 4 digit #s don't sort to the top.

Apr 26th, 2002, 02:12 PM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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.

Apr 26th, 2002, 02:13 PM	#6
hisdwelling New Member Join Date: Apr 2002 Posts: 16	Steve, I've tried that too... same result.

Apr 26th, 2002, 02:14 PM	#7
hisdwelling New Member Join Date: Apr 2002 Posts: 16	Steve, I've tried that too... same result.

Apr 26th, 2002, 02:15 PM	#9
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	=VLOOKUP(A2,Sheet2!$A$2:$B$4210,2) should be=VLOOKUP(A2,'Sheet2'!$A$2:$B$4210,2)

Apr 26th, 2002, 02:17 PM	#10
hisdwelling New Member Join Date: Apr 2002 Posts: 16	I ran the =trim function on all of the unit# cells. I know there aren't any leading or trailing spaces.