lookup i think!

  • Thread starter Thread starter Imran S
  • Start date Start date
I

Imran S

Hi I am trying to set up a simple function.

i want a list of paints see example below. I have managed to create a
list using a validation function. What i can't seem to do is to
display a price to the right of the item. So when i select an item
from the list it picks a price which i store in the same workbook as
the products list and it should appear in the cell to the right. For
example if someone picks red paint then the price for that appears in
the same row, one or two rows to the right. I want to link the
product with a price.


red paint 10.00
blue paint 34.00
green paint 90.00

Please can someone help. Thank you.

Regards,

Imran
 
Imran,

let's say your value is in A1, the tab le in H1:J3, then in B1 to get the
price, use

=VLOOKUP(A1,$H$1:$I$3,2,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Imran

If your data is on Sheet2 in cells A2:B4 and your Selection from the drop
down validation is on Sheet 1 in cell A2, put this formual in Sheet1 cell B2

=VLOOKUP(A2,Sheet2!$A$2:$B$4,2,0)
Change ranges to suit
 
Thank you very much. It worked perfectly.


Roger Govier said:
Hi Imran

If your data is on Sheet2 in cells A2:B4 and your Selection from the drop
down validation is on Sheet 1 in cell A2, put this formual in Sheet1 cell B2

=VLOOKUP(A2,Sheet2!$A$2:$B$4,2,0)
Change ranges to suit
Click to expand...
 
Hi,

i have another question. if i add another column to my table in sheet
2. e.g. see below.

poduct cost price selling price

red paint 10 20
green paint 20 30
blue paint 30 40

and when i put the formula in sheet 1 instead of just putting the cost
price lookup in a cell could i have the selling price also appear for
the same lookup in the next column along?

regards

imran
 
Hi Imran

Yes you can
=VLOOKUP(A2,Sheet2!$A$2:$B$4,3,0)

Change the 2 to 3.
This tells VLookup to return the value in the 3rd column to the right of the
value "looked up", where column column 1 is the column containing the lookup
value.
 
Back
Top