Double lookup from two dimensional table

[bujaman]

Hello all,
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table. For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted, so hopefully that will help. Thanks for any help anyone is able to give.

[NBVC]

It looks like the H/W sizes for each material are 6" apart...so perhaps we can use that fact along with the Ceiling() function to always go to the next size (unless of course the exact size is selected)...

Try:

Please Login or Register  to view this content.

[bujaman]

Thanks for the quick reply, that works well, however, sometimes the difference between the sizes may vary...so, with that being said, any ideas? Thanks again, I really appreciate it.

[NBVC]

....I knew it was to easy to be true.....

Try:

Please Login or Register  to view this content.

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and you will see { } brackets appear around it... then copy it down.

[bujaman]

Thank you so much! That works perfectly! That is exactly what I needed.

[bujaman]

OK, I am visiting this problem again. I would now like to turn this into a custom function. How would I go about doing that?