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

Hello,

Please use this formula: =INDEX(B2:E6,MATCH(B9,A2:A6,0),MATCH(INDEX(B1:E1,MATCH(MIN(ABS(B1:E1-B10)),ABS(B1:E1-B10),0)),B1:E1,0))

Best Regards,
Chitrahaas

Answer 2

I am only allowed to use the formulas INDEX and MATCH.

This is the assignment I am trying to solve:

What different formula can be created so that it satisfies this criteria?

Answer 3

Hello,

To your requirement, you need to get the closest value to the range B1:E1, and to get the closest value, we can't just use the INDEX and MATCH function, where we also need to use ABS and MIN functions to meet your requirement.

Note: We can also meet your requirement via VLOOKUP but as the question to find using INDEX and MATCH, I think you can go ahead with the formula I shared and check with the teacher.

The formula I shared will meet your exact requirement:

Best Regards,

Chitrahaas

Answer 4

Hello,

If you need any further help, please feel free to post back to us.

Thanks,
Chitrahaas

Answer 5

Hi,

Rearrange the sizes (B1:E1) in descending order and try this formula

=INDEX(B2:E6,MATCH(B9,B1:E1,-1),MATCH(B8,A2:A6,0))

Hope this helps.