# V/HLookup

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

R

#### Roger Govier

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

J

#### Jim Thomlinson

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

2

#### 2010charliep

Works perfectly - thanks!!