Looking Up Closest Match Between Two Columns and Within That Row

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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