Ive tried a few variations on index and match to return a value on 2 sets of criteria but am having issues.
In the example below the user will enter the product family name in A2 and enter an anticipated volume in B2. I would like to have the formula find the correct product group in column A, then look for the value that is less than or equal to that in column B and use the price associated with that in column C.
In this instance entering NN and 6000 would return a price of 50 in C2.
Any help would be greatly appreciated. I can rearrange how the data table is laid out if needed.
<tbody>
</tbody>
In the example below the user will enter the product family name in A2 and enter an anticipated volume in B2. I would like to have the formula find the correct product group in column A, then look for the value that is less than or equal to that in column B and use the price associated with that in column C.
In this instance entering NN and 6000 would return a price of 50 in C2.
Any help would be greatly appreciated. I can rearrange how the data table is laid out if needed.
A | B | C | ||
1 | Product Family | Volume | Price | |
2 | NN | 6000 | ??? | |
3 | PRD FAm | Tiers | Price | |
4 | SS | 0 | 20 | |
5 | SS | 2000 | 15 | |
6 | SS | 10000 | 10 | |
7 | NN | 0 | 60 | |
8 | NN | 5000 | 50 | |
9 | NN | 20000 | 40 | |
10 | SR | 0 | 6 | |
11 | SR | 50000 | 4 | |
12 | SR | 100000 | 2 | |
13 | ||||
<tbody>
</tbody>