Return a value using 2 criteria - Index and match?

[MikeGeno]

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.

	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>

 

[pgc01]

Hi Mike

Assuming that the table has the volumes in ascending order for each product family, like in your example, try in C2:

=LOOKUP(2,1/(A4:A12=A2)/(B4:B12<=B2),C4:C12)

 

[MikeGeno]

pgc,

thank you very much this works perfectly!

 

[pgc01]

I'm glad it helped. Thank you for the feedback.