0
votes

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:

Fruit Pricing Graph

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

Results

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!

1
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

1 Answers

3
votes

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

enter image description here

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.

enter image description here