Finding closest match to values in a range

[Marcus]

Hi there,

I have a number of template/master sizes (of poster ads, as it happens) and a whole range of actual poster sizes I need to adapt to from one of these template/master sizes. I have worked out the aspect ratio of each template/master, and the aspect ratio of each size required. What I want to do is insert a formula so that for each size I have to create it tells me which is the closest template/master match. If there are two template/masters that are an identical distance away I would like to use the template/master that has the lower aspect ratio. I have attached a simple spreadsheet which has a simplified example of what I am looking to do. It has a range of Template (Master) Names with the aspect ratio of each template/master in the row underneath. I also have two columns of poster sizes required, with a third column that calculates the aspect ration for each size required. I have a fourth column which is where I want the Template/Master name to appear as a result of the formula. For now I have manually entered the correct result - because I don;t know how to write the formula. I have played around with Match and Index and Lookup for ages but just cannot get it to do what I want. All and any hep gratefully appreciated.

[Bob Phillips]

Bit convoluted but here goes.

Insert two new rows 2:3, with values in C2:C3 of Height and Width
Insert a new column in D with a value of '-999x999 in D1

In D2, =VALUE(LEFT(D1,FIND("x",D1)-1)) copied across to K2
In D3, =VALUE(MID(D1,FIND("x",D1)+1,99)) copied across to K3

In E8, add this array formula
=MATCH(MIN(ABS(IF($D$2:$K$2=INDEX($D$2:$K$2,MATCH(MIN(ABS($D$2:$K$2-A8)),ABS($D$2:$K$2-A8),0)),$D$3:$K$3,9999)-B8)),ABS(IF($D$2:$K$2=INDEX($D$2:$K$2,MATCH(MIN(ABS($D$2:$K$2-A8)),ABS($D$2:$K$2-A8),0)),$D$3:$K$3,9999)-B8),0)
and copy down to E24

In D8, =INDEX($D$1:$K$1,E8) and copy down to E24

I get different results to you after D21.

[Marcus]

Hi Bob,

thanks so much for your suggestion - I have tried it but do not seem to be getting sensible results (in fact D824 and E8:E24 are all showing #N/A).

I have assumed that on your second last line you meant copy down to D24 not E24??

I have attached my spreadsheet (V2) after making the suggestions you have proposed (or at least I think I have).

Any further suggestions gratefully received and thanks again for the help so far.

Regards,

Marcus

[Bob Phillips]

Marcus, you simply didn't array-enter the formula in E8. Edit the formula, and hit Ctrl-Shift-Enter, not just enter - you will see the formula in the formula bar embraced by braces , that is {=formula}. Should be what I gave you then.