V/HLookup

  • Thread starter Thread starter 2010charliep
  • Start date Start date
2

2010charliep

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
 
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
 
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))
 
Back
Top