Need advice creating a formula to solve a problem

[shadestreet]

Background:
I need to quickly determine how many layers of cases we have on our pallets for 2,000 SKU's. Each SKU has a standardized configuration, so the number of layers per pallet, cases-per-pallet, and cases-per-layer are fixed and not variable.

Here is what I know:
-Case length, width, and height
-Cases per pallet
-Height range for pallets (average is 54 inches with low deviation)

I believe that I should be able to develop some logic that can figure this out for me. Here is my strategy so far:

1. I developed a table with the following columns:
-A: SKU reference number (unique)
-B: cases/pallet
-C: SKU height
-D: formula: =54/sku height to get a target value for the number of layers

-E through M I divide the cases/pallet number in B by 2, then 3, then 4, etc until I get to column M where I divide by 10. The purpose is to find the possible divisors that could be the true cases per layer.

The trick with columns E through M is that only values that are integers could be possibilities, so I used an "IF" statement along with MOD function to only complete the calculation if the MOD = 0. This way in columns E through M I only get 1-3 different possibilities.

Now at this point I could go through the list and scanning through E-M find the value that makes the most sense. My logic whichever value in E-M is closest to the number in column D is the likely cases/pallet.

However I cannot think of a good function or set of functions to do this automatically for me. I tried using HLOOKUP and setting it to "TRUE" so it finds the closest match to column D, but it isn't finding the closest match at all...

any advice?

[shadestreet]

Well after some more thought I realized how to solve this. It was quite complicated, but effective.

Basically in columns E through M I decided to return the "standard deviation" between the target value in column D and the quotient originally round in E through M.

Then I returned the min value from E through M, and used this as my lookup reference number when doing the hlookup so I could use exact match instead of closest match.