Prev: I need an expense Db with categories for Schedule C
Next: Excel 2007 - 2nd Request
From: 2010charliep on 22 Mar 2010 12:02
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
From: Roger Govier on 22 Mar 2010 12:15
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
>
From: Jim Thomlinson on 22 Mar 2010 12:30
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
From: 2010charliep on 23 Mar 2010 06:34
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
 | 
Pages: 1
Prev: I need an expense Db with categories for Schedule C
Next: Excel 2007 - 2nd Request