I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook, then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook, then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

Yes

Perhaps a Data Validation Dropdown list in A2 would help?

See Debra Dalgleish's site for help on Data Validation lists.

http://www.contextures.on.ca/xlDataVal01.html


Gord Dibben MS Excel MVP


On Tue, 19 Aug 2008 12:15:01 -0700, vburns
wrote:

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook, then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

Too hasty with the first post.

If your list of items is in a range of say, A2:A800, simply enter Bernie's
formula in B2 then copy down to B800 by double-click on the fill handle of
B2.


Gord

On Tue, 19 Aug 2008 12:15:01 -0700, vburns
wrote:

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook, then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

Thank you for your help. I'm determined to make this work, but I can't yet
get it to work.

My common item numbers are in column F in both worksheets.

I want my price returned from column G in the other price list to column P
of my worksheet. This is my formula:

=VLOOKUP(F7:F651,'08pricelist'!F:G,7,FALSE)

Can you tell what I've done wrong? I get the #REF! error.

"Gord Dibben" wrote:

Too hasty with the first post.

If your list of items is in a range of say, A2:A800, simply enter Bernie's
formula in B2 then copy down to B800 by double-click on the fill handle of
B2.


Gord

On Tue, 19 Aug 2008 12:15:01 -0700, vburns
wrote:

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook, then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

In P7, enter the formula:

=VLOOKUP(F7,'08pricelist'!F:G,2,FALSE)

and copy down as far as your need.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
Thank you for your help. I'm determined to make this work, but I can't yet
get it to work.

My common item numbers are in column F in both worksheets.

I want my price returned from column G in the other price list to column P
of my worksheet. This is my formula:

=VLOOKUP(F7:F651,'08pricelist'!F:G,7,FALSE)

Can you tell what I've done wrong? I get the #REF! error.

"Gord Dibben" wrote:

Too hasty with the first post.

If your list of items is in a range of say, A2:A800, simply enter Bernie's
formula in B2 then copy down to B800 by double-click on the fill handle of
B2.


Gord

On Tue, 19 Aug 2008 12:15:01 -0700, vburns
wrote:

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook,
then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

Thank you so much. I don't understand the significance of the column index
value of 2, but this worked for most items. Not all prices are in column G
on the price sheet so those items did not return a price, but I'll go back
and tweak those.

"Bernie Deitrick" wrote:

In P7, enter the formula:

=VLOOKUP(F7,'08pricelist'!F:G,2,FALSE)

and copy down as far as your need.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
Thank you for your help. I'm determined to make this work, but I can't yet
get it to work.

My common item numbers are in column F in both worksheets.

I want my price returned from column G in the other price list to column P
of my worksheet. This is my formula:

=VLOOKUP(F7:F651,'08pricelist'!F:G,7,FALSE)

Can you tell what I've done wrong? I get the #REF! error.

"Gord Dibben" wrote:

Too hasty with the first post.

If your list of items is in a range of say, A2:A800, simply enter Bernie's
formula in B2 then copy down to B800 by double-click on the fill handle of
B2.


Gord

On Tue, 19 Aug 2008 12:15:01 -0700, vburns
wrote:

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate workbook,
then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?

VLOOKUP takes 3 arguments, with an optional fourth. The third argument is the column of the table
to extract the data from - it cannot be a number higher than the number of columns in the table.
Since your table is columns F and G, you only have 2 columns of data, and so 2 is the proper value
for the third argument, not 7. Check out help on VLOOKUP for more.

Not all prices are in column G

They should all be in column G - that is proper usage of a database.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
Thank you so much. I don't understand the significance of the column index
value of 2, but this worked for most items. Not all prices are in column G
on the price sheet so those items did not return a price, but I'll go back
and tweak those.

"Bernie Deitrick" wrote:

In P7, enter the formula:

=VLOOKUP(F7,'08pricelist'!F:G,2,FALSE)

and copy down as far as your need.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
Thank you for your help. I'm determined to make this work, but I can't yet
get it to work.

My common item numbers are in column F in both worksheets.

I want my price returned from column G in the other price list to column P
of my worksheet. This is my formula:

=VLOOKUP(F7:F651,'08pricelist'!F:G,7,FALSE)

Can you tell what I've done wrong? I get the #REF! error.

"Gord Dibben" wrote:

Too hasty with the first post.

If your list of items is in a range of say, A2:A800, simply enter Bernie's
formula in B2 then copy down to B800 by double-click on the fill handle of
B2.


Gord

On Tue, 19 Aug 2008 12:15:01 -0700, vburns
wrote:

Does this mean I still have to type in the item number each time in cell A2
to return the price from the other worksheet?

"Bernie Deitrick" wrote:

Let's say that your common item numbers are both in column A

=VLOOKUP(A2,OtherSheet!A:H,8,False)

will pull the data from column H of the OtherSheet. If you actually have a separate
workbook,
then
the formula will be slightly different, but not significantly.

HTH,
Bernie
MS Excel MVP


"vburns" wrote in message
...
I have a list of 800 items I must look up from one worksheet and enter the
price on another worksheet. The worksheets are not similar. Using a common
item number that appears in both worksheets, will VLOOKUP do this for me? In
other words copy a price from one worksheet to the proper column in another
worksheet?