Index-Match with Multiple Criteria + Ranges

Question

I'm wanting to find a price, based of off a type and range.

For instance, here is a graph showing how much a specific fruit costs based on the amount purchased or type:

Here is what the worksheet would look like (TOP) and what the expect result would be (BOTTOM):

I'm having an issue figuring out how to use a criteria and also a range.

If "Regular"- determine range and determine pricing

If "Organic" or "Import" - use set pricing

I tried using INDEX MATCH but am unable to figure out how exactly to work this. Any help on this would be greatly appreciated. Thanks!

See Why Not Images of Code and Sample Data and Discourage screenshots of code and/or errors. — user4039065
Sorry to say but B1:H1 is just plain bad worksheet design if the labels are to be used for any type of lookup. Possible fix: retain the lower end of the range and use custom number formatting to display the upper end while discarding it from any calculation. e.g. 0\-\1\5\0 for 85-150. — user4039065
Yeah, I realize this and is not set up that way, it was just for the example. Thanks for that tid bit though! — Solomon3y

Scott Craner Scott Craner · Accepted Answer · 2016-03-14T19:21:42

First change the top row of you data to only include the start of the range:

The use this INDEX/MATCH:

=INDEX(Sheet4!$B$2:$H$4,MATCH(B2,Sheet4!$A$2:$A$4,0),IF(C2="Regular",MATCH(D2,Sheet4!$B$1:$F$1),MATCH(C2,Sheet4!$B$1:$H$1,0)))

Where 'Sheet4' is the name of the sheet with the data.