VLOOPUP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fran - 08 Jun 2006 03:20 GMT

How do I lookup info on one worksheet and display on another worksheet?

One worksheet1 is product info: item #, product, and price.

On worksheet2, I want to use VLOOKUP to lookup the item # that is entered
into cell A2 on the second worksheet and display the product and price in
cells A3 and A4.

Reply to this Message

adrian# - 08 Jun 2006 04:22 GMT

Name the range of the product info and make sure you don't include the
headings. Easy way to create a range name is to select all cells in the
range, then click in the Name box which is on the left side of the Formula
bar. Type in a name (no spaces allowed in name) and press enter.

Create you vlookup formula and when you need to put in the 2nd arguement (ie
the range to look in) press the function key F3 (for paste name) and your
Named Range should be in the list. Click it and go OK. Then complete the
rest of the formula (ie =vlookup(A2,Named Range,2,False). The number in
the formula refers to the columns going to the right in your Named Range.
Hope this helps. Adrian.

> How do I lookup info on one worksheet and display on another worksheet?
>
[quoted text clipped - 3 lines]
> into cell A2 on the second worksheet and display the product and price in
> cells A3 and A4.

Reply to this Message

Fran - 08 Jun 2006 04:35 GMT

This is what I have ???? I am totally lost.

=VLOOKUP("B5.'Product List'!'Product List'!A2:A101",'Product
List'!A2:A101,'Step-7 Product Lookup'!C7:C10)

> Name the range of the product info and make sure you don't include the
> headings. Easy way to create a range name is to select all cells in the
[quoted text clipped - 15 lines]
>> into cell A2 on the second worksheet and display the product and price in
>> cells A3 and A4.

Reply to this Message

adrian# - 08 Jun 2006 05:01 GMT

Delete the formula and start again. First make your Named Range as
suggested before. Then click in cell where you what the result to appear.
Type in your new formula as below.

=vlookup(value to lookup,Named Range,column number of what you are
retrieving, False)

1st arguement => value to lookup. This should be a cell reference of an
entered item.
2nd arguemnt => Named Range. As already described this will refer to your
range on the other worksheet.
3rd arguement=> column number of what you are retrieving. 1 is for the 1st
column within your Named Range, 2 is for the 2nd column within your Named
Range, etc. going across to the right.
4th arguement=>False. This means that you want an exact match to your
lookup value. If it cannot find one then you will get an error.

> This is what I have ???? I am totally lost.
>
[quoted text clipped - 20 lines]
>>> entered into cell A2 on the second worksheet and display the product and
>>> price in cells A3 and A4.

Reply to this Message

Fran - 08 Jun 2006 06:33 GMT

I am still not getting it to work
=VLOOKUP(B5,'Product List'!A1:E101,3,FALSE)

I want to type a product number in cell B5 and I want it to display the type
in cell C7, the model in cell C8, Shipping in cell C9 and the price in cell
C10.
This info is looked up on the Product List which is a different worksheet in
cells A1:E101

> Delete the formula and start again. First make your Named Range as
> suggested before. Then click in cell where you what the result to appear.
[quoted text clipped - 38 lines]
>>>> entered into cell A2 on the second worksheet and display the product
>>>> and price in cells A3 and A4.

Reply to this Message

adrian# - 08 Jun 2006 06:43 GMT

OK. Your formula looks good. What error are you getting?
You need to make sure the cell format (ie Text, Number, General, etc) is the
same in the cell B5 that you enter as the first column in your range on the
other worksheet (A1). Adrian.

>I am still not getting it to work
> =VLOOKUP(B5,'Product List'!A1:E101,3,FALSE)
[quoted text clipped - 48 lines]
>>>>> entered into cell A2 on the second worksheet and display the product
>>>>> and price in cells A3 and A4.

Reply to this Message

Roger Govier - 08 Jun 2006 08:12 GMT

Hi Fran
Presumably your product list has the following
A Product Number
B Type
C Model
D Shipping
E Price
If so, then in cell C7 of your sheet enter
=VLOOKUP($B$5,'Product List'!A1:E101,ROW(2:2),FALSE)
Copy down through cells C8:C10

Your first offset needs to be 2 to pick up Type from the table. By using
ROW(2:2) instead of 2, it will automatically increment to 3, 4 and 5 as
you copy down.

To protect against errors, it would be better to use
=IF(ISERROR(VLOOKUP($B$5,'Product List'!A1:E101,ROW(2:2),0)),"",
VLOOKUP($B$5,'Product List'!A1:E101,ROW(2:2),0))

(I have replaced the FALSE with 0 as it makes the formula slightly
shorter)

Signature

Regards

Roger Govier

>I am still not getting it to work
> =VLOOKUP(B5,'Product List'!A1:E101,3,FALSE)
[quoted text clipped - 49 lines]
>>>>> entered into cell A2 on the second worksheet and display the
>>>>> product and price in cells A3 and A4.

Reply to this Message