V/HLookup
 

I have a table of product codes with 20 different prices for each product
code that sit on sheet 2 of the workbook.
Price Range
prod code 1 2 3 4
a 10 20 30 40
b 15 25 35 45
c 17 19 21 22

On Sheet 1, in cells A6 to A50 I want to enter various product codes, and
depending on what price range I have entered in cell A1, I would like to be
able to return the price next to the product code in column B
ie:
Price range A1 =4
Product code=A6 =b
Returns price of 45 in B6

Price range A1 =2
Product code=A7=c
Returns price of 19 in B7

If anyone can help me simplify this using lookup table it would be great

Thanks



If anyone can help

--
charliep

V/HLookup
 

Hi

In B6 on Sheet1 enter
=IF($A6="","",INDEX(Sheet2!$A:$S,MATCH($A6,Sheet2! $A:$A,0),$A$1+1))
Copy down as required

--
Regards
Roger Govier

2010charliep wrote:
I have a table of product codes with 20 different prices for each product
code that sit on sheet 2 of the workbook.
Price Range
prod code 1 2 3 4
a 10 20 30 40
b 15 25 35 45
c 17 19 21 22

On Sheet 1, in cells A6 to A50 I want to enter various product codes, and
depending on what price range I have entered in cell A1, I would like to be
able to return the price next to the product code in column B
ie:
Price range A1 =4
Product code=A6 =b
Returns price of 45 in B6

Price range A1 =2
Product code=A7=c
Returns price of 19 in B7

If anyone can help me simplify this using lookup table it would be great

Thanks



If anyone can help

V/HLookup
 

With my Price Code in A1 and the product code in A2 I use this formula

=INDEX($B$6:$E$50, MATCH($A$2, $A$6:$A$50, 0), MATCH($A$1, $B$5:$E$5, 0))
--
HTH...

Jim Thomlinson


"2010charliep" wrote:

I have a table of product codes with 20 different prices for each product
code that sit on sheet 2 of the workbook.
Price Range
prod code 1 2 3 4
a 10 20 30 40
b 15 25 35 45
c 17 19 21 22

On Sheet 1, in cells A6 to A50 I want to enter various product codes, and
depending on what price range I have entered in cell A1, I would like to be
able to return the price next to the product code in column B
ie:
Price range A1 =4
Product code=A6 =b
Returns price of 45 in B6

Price range A1 =2
Product code=A7=c
Returns price of 19 in B7

If anyone can help me simplify this using lookup table it would be great

Thanks



If anyone can help

--
charliep

V/HLookup
 

Works perfectly - thanks!!
--
charliep


"2010charliep" wrote:

I have a table of product codes with 20 different prices for each product
code that sit on sheet 2 of the workbook.
Price Range
prod code 1 2 3 4
a 10 20 30 40
b 15 25 35 45
c 17 19 21 22

On Sheet 1, in cells A6 to A50 I want to enter various product codes, and
depending on what price range I have entered in cell A1, I would like to be
able to return the price next to the product code in column B
ie:
Price range A1 =4
Product code=A6 =b
Returns price of 45 in B6

Price range A1 =2
Product code=A7=c
Returns price of 19 in B7

If anyone can help me simplify this using lookup table it would be great

Thanks



If anyone can help

--
charliep