determine correct cell and color based on four parameters.

I need a macro or series of macros to determine the correct cell and then color to return a number for a product. In the attached file the correct answer would be D5. The parameters in M5-N7 determine what part of the table to look at then I need to find the closest number to N8 in the chart without N8 being larger than it.

So, it should be looking at D5:D6 in the table then D5 would be the correct answer. But i am unsure how to do this in a macro or formula for the entire chart.


Thanks in advance for any help.

Let's assume that the user has selected:
Pickup Ø = 50
Suction Line = 5
Elbows = 2
Trim Speed = 900

What value should be returned from the range E5:E6 when Trim Speed is greater than the allowed values (900>850)? Or should an error be returned?

Artik

Thanks for your reply.

In that case, I would want to either return N/A or an error.

I think just the cell color will work. it shows two different models so i can use the color to determine which one.

Thanks for all your help. I think i just have one more thing. How would i modify this function to be able to do this operation on two different sheets. I tried to just copy the function to the next sheet, but it still seems to be looking at sheet one. I have attached a sample for reference.

It is just the two sheets didn't think about it originally needing the second sheet.

that works thank you again for all the help.