Value Not Available in Spreadsheet - Index and Match functions

Question

I am creating a sheet in which you type in the input values of corresponding size and quantity and it yields the total price.

For example, if I typed in a minimum quantity of 45, the price for a quantity of 48 would appear.

However, I have a value not available error in cell B10 where I made the formula.

The formula I used was: =INDEX(B2:E6,MATCH(B9,A2:A6,1),MATCH(B8,B1:E1,0))

This is what my spreadsheet looks like:

What would need to be fixed in order for this to work?

The only formulas I can use are INDEX and MATCH. This is what I need to do:

Thank you!

Answer 1

I have one more question for you.

Why do the formulas MIN and ABS need to be used in this INDEX and MATCH problem?

What are the roles of all these formulas in the problem so that it works?

Thank you!

Answer 2

I am confused on what the minimum quantity means in the question?

For example. if I ordered 40 medium shirts would the price be $24.03 or $44.45?

What would make the most sense in the context of this problem?

Thank you!

Answer 3

Hello,

ABS function in the Excel application will give you the Absolute result, for example, the difference between two numbers gives you an output of a negative number, using ABS function, it will give you the output result.

MIN function in the Excel application will give you the closest match. Since your output needs to be considered based on the nearest value, I used the MIN function.

References:
MATCH function (microsoft.com)

ABS function (microsoft.com)

In your sample data, the orders are 12, 24, 48, and 96. So, if the minimum quantity is 40, it will take 48 and give the output based on the size. For example, if 40 is the minimum quantity, then you will get the following answer based on the sizes:

Size	48
XS	27.34
S	34.18
M	44.45
L	58.14
XL	75.25

Regards,
Chitrahaas