Spreadsheet: 454540d1459876977-lookup-match-and-a-number-range-example
Sheet: Tab1

 |  |  |  |  |  |  |  | Product Code |  |  |  |  |  |  |  |  |  |  | Quantity |  |  |  |  |  |  | Current Rate
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 315 |  |  |  |  |  |  | 700
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 1 |  |  |  |  |  |  | 1000
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 390 |  |  |  |  |  |  | 700
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 3 |  |  |  |  |  |  | 1000
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 10000 |  |  |  |  |  |  | 300
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 650 |  |  |  |  |  |  | 500
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 350 |  |  |  |  |  |  | 700
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 250 |  |  |  |  |  |  | 700
 |  |  |  |  |  |  |  | Product 1 |  |  |  |  |  |  |  |  |  |  | 2500 |  |  |  |  |  |  | 400
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 61 |  |  |  |  |  |  | 5000
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 300 |  |  |  |  |  |  | 4000
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 50000 |  |  |  |  |  |  | 2500
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 10 |  |  |  |  |  |  | FORMULA HERE
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 10
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 25
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 50
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 22
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 4
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 25
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 3
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 2
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 10436
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 6296
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 4030
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 7008
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 2702
 |  |  |  |  |  |  |  | Product 2 |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 10436
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 7008
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 34
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 160
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 400
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 4
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 2767
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 20
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 440
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 300
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 408
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 196
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 132
 |  |  |  |  |  |  |  | Product 3 |  |  |  |  |  |  |  |  |  |  | 31