Results 1 to 4 of 4

Thread: Finding closest match to values in a range

  1. #1

    Question Finding closest match to values in a range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    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.

  3. #3

    Thanks Bob - but it doesn't appear to work for me

    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
    Attached Files Attached Files
    Last edited by NBVC; 2014-03-20 at 07:31 PM. Reason: deleted quote of previous post

  4. #4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •