EXCEL: Lookup Table based on Inputs and Provide Corresponding Output Value based on BEST FIT

Am looking for help regarding the below Size Predictor for Apparels

I have a standardized table which has the Dress Sizes namely S, M, L, XL, 2-XL, 3-XL and the associated measurements of key body parameters which are mapped to the standard industry sizes. This is the size chart which is a standardized with many of the apparel manufacturers.

The idea is the user will provide in the input box the actual measurements of self. Based on the inputs, all the 3 inputs will be checked against the respective columns and will provide the output which size is the best fit for the user.

Important aspect is, each input parameter provided by the user must not be lesser than the value in the corresponding columns in the table as this will result in the apparel being too tight in various body positions. The apparel should have a slack fit or perfect fit in all the various body positions. The result should give the corresponding sizes where all the 3 inputs less than or equal to the corresponding values in the the size table to ensure a loose or perfect fit.

E.g. As in the example given below bust size of 112 which is the input, Small will not be correct since 112 is higher than 109, but size Medium will fit. But if I choose Medium then Waist size of 101 is higher than size of 96 in Medium which means Medium will be a tight fit in the waist so I have to look for Large where 101 is lesser than 102 and will have a slack fit of 1 cm. But if I consider the Hip size input of 128 against Medium which is a slack fit in Bust and Waist, in Hip 128 is higher than 127, which means though Bust and Waist are a slack fit in Medium size the dress might not still fit me at Hip so I have to select X-Large.

So at X*-Large* the Bust will have a slack of 12cm, Waist a slack of 6cm and Hip a slack of 4cm. So the output for me should be suggesting a size X-Large for the dimensions I input.

So I need a formula in the output box where the input is being progressively checked in the table to show the closet slack fit of dress size for the dimensions of the body.

Requesting Experts to help here please

Example of the Size Chart, Input and Output Required

I would find all three fits with match() then choose the largest: First reverse the sorting of your table, so it goes from 2XL to Small, instead of Small to 2XL. Now use:

=index(a2:a7, min(match(g2, b2:b7,-1),match(g3, c2:c7,-1),match(g4, d2:d7,-1)))

How it works:

Find the "Bust" match like this: =match(g2, b2:b7,-1) (Note: I am assuming that "Size" is cell A1 in your picture.")

By default match matches the first row with a value less than or equal to your lookup. You want the last row where it is greater than or equal to your input. We can't do a "last row" match, so reverse the table and use the , -1 flag to find the first row with a value greater than or equal to your lookup.

Then do this for all three, and wrap in a min:

=min(match(g2, b2:b7,-1),match(g3, c2:c7,-1),match(g4, d2:d7,-1))

Then wrap index() around them to get the name of the size that controlled..

=index(a2:a7, min(match(g2, b2:b7,-1),match(g3, c2:c7,-1),match(g4, d2:d7,-1)))