Looking Up Closest Match Between Two Columns and Within That Row

    I'm usually fairly decent when it comes to Excel formulae but this one has me stumped. I've been given a table (Columns K-P) and a "current value" and "percent off" (Columns R and S). Using this information, I have to find where the percent off (column S) falls in the table, check that row for what's closest to the current value (Column R) and return the header information (Column T).


    Is this even possible? Any help would be very appreciated.


    [ATTACH=CONFIG]70416[/ATTACH]

    Re: Looking Up Closest Match Between Two Columns and Within That Row


    I don't quite understand logic for your given outputs, but I think formula would be like this.


    =INDEX($M$1:$P$1, MATCH(R2, INDEX($L$2:$O$7, MATCH(S2, $K$1:$K$7, 1)), 1))


    The first MATCH uses L:O because we need to have a starting value in our array that can be less than search value, so we'll "cheat" and use the percetage column.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login