Vlookup two tables to choose from 3rd table

I have a spreadsheet where I have two tables A5:C15 and A16 to C26. These tables calculate all the possible options for an item across a sheet and all the possible options down the sheet. I some how need to automatically calculate the "best" option to fit on a few different sheet sizes E5:F13. So in the attached the best option is 5 down and 3 across and that size would be 27.5625 x 31.5. These numbers will change based on sizes that are input on another screen but for example I am posting one example/ For this example in real life and manually I would want it to be associated with the 28 x 35 in E5:F13. Is there a formula to automatically select the max sizes from C6:C15 (in this case 27.5625) and max size from C17:C26 (in this case 31.5) and have it select the best option from E5:F13. The table from E5:F13 the width and length dimensions are tied together so the options are 18 x 22, 20 x 26, 27 x 32, 28 x 35 and 28 x 40. So it cant look at the table and then select 28 from the width and then 32 from the length. Hopefully this makes sense and can be done. Im sure it can but I have been working in the spreadsheet for a couple hours and my mind is drawing a blank.

I apologize if the title is correctly worded too

These numbers will change based on sizes that are input on another screen

If the hypothetical numbers from another screen were in I2:J2 as in the attached, then array enter these two formulas in I3 and J3.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Since the area of the Sheet sizes on the table increases it would seem that formulas could be set up to find the smallest area that is greater than or equal to the smallest sheet sizes that can be calculated.
The following proposed solution uses a number of helpers, which may be moved and/or hidden for aesthetic purposes.
The helper that gets the smallest table size for 'Sheet Size Down' is:

Formula:

Please Login or Register  to view this content.

The helper that gets the smallest table size for 'Sheet Size Across' is:

Formula:

Please Login or Register  to view this content.

The formula that returns the actual sheet width is:

Formula:

Please Login or Register  to view this content.

The formula that returns the actual sheet length is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I believe that the part of the first two formulas that reads ...MATCH(MAX(C$6:C$15)... and ...MATCH(MAX(C$17:C$26)... takes care of that.
However the formula in I3 does need to be modified so that it reads:

Formula:

Please Login or Register  to view this content.

Once the formula has been entered, copy it across to J3.
If the solution isn't yielding the correct results when the dimensions change, please provide an example of the values in A3 and B3 that cause the failure as well as the expected results for I3 and J3.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Ok after working on this a bit to make it fit to my real life situation (multiple materials and multiple sizes within those materials) I am coming up with some #REF and #N/A that hopefully you can help with. I realize it may be an issue for grouping m13:r16 since the numbers in P aren't in order if I understand what I read on index and match formulas. I tried to adjust the formula and removed row 16 all together and it is the same result as whats attached. Each material group is separated with bold grid line. The separation is by weights. The size that will be changing is in cell C3 and D3. In the actual spreadsheet it is a formula referencing another cell in another tab but due to sensitive info in that tab I had to clean it up and just provide the tab I am working in. Based on the original size of 5 x 10 I only have a few cells with the errors but if I change to 5.125 x 6.25 I get all but 2 cells with the errors. Any suggestions? Thanks

Some modifications have been made to the formulas, so that I believe that the expected results for 5 1/8 x 6 1/4 are being returned in the 'By Table' range.
Let us know if you have any questions.

You're Welcome. If you see any other issues reply back to this thread (I'll get notified), or start a new one (the more eyes on a problem the better) and include a link to this one.

See if the following array entered formula*, pasted into cell S15, helps with that particular issue:

Formula:

Please Login or Register  to view this content.

*As Dave stated, array entered formulas must be activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode.
Let us know if you have any questions.

I tested this array entered formula in R29 and it returned the result I expected (28):

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Why would somebody just entering data need to enter an array formula? You seem to be saying that you will set up all the calculated fields in advance, so just protect the sheet.

The latter - it's just the way you enter that type of formula. Once entered, they behave just the same as any other formula.

Yes, the formula for S15 should be copied, pasted and modified for the other cells in column S. Likewise for the formula for the formula for R29. The -28 and -14 are there to make sure that the 'ROW' array starts at 1 (select S15 and run the Evaluate Formula feature found on the Formulas tab). You could also S15 formula to read (likewise for the R29 formula):

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Here is the array entered formula that should go into R7:

Formula:

Please Login or Register  to view this content.

Once that formula is activated by simultaneously pressing the Ctrl, Shift and Enter keys, S7 displays the correct value (34).
Let us know if you have any questions.

You're Welcome and thank you for the feedback. If the modified formula solves the issue, please take a moment to mark the thread as 'Solved'. I hope that you have a blessed day.