Search code examples
excelexcel-formulaexcel-match

MATCH with a running lookup_value

I have data as follows: enter image description here

A person can retire when their accumulated corpus is more than required corpus. Hence, for the example shown above, the retirement age will be 55 (i.e., value of E1).

I am having trouble finding the correct formula to locate the column where value in row 2 becomes less than that of row 3.

I tried =MATCH(B2:G2, B3:G3, 1) for the "less than" match but no luck. Please help.

Solution

  • Try this:

    =INDEX($B$1:$G$1,SUMPRODUCT(MATCH(TRUE,B3:G3>B2:G2,0)))

    it returns the following, as required:

    enter image description here

    What's happening here? This line (below) is an array formula which creates an array like {FALSE,FALSE,FALSE,TRUE,TRUE,TRUE}

    =B3:G3>B2:G2

    However, MATCH doesn't know how to evaluate the array unless you enter it as an array formula which would usually require entering the formula using Ctrl+Shift+Enter rather than just Enter. A nice trick to avoid having to do this is to wrap the array in a SUMPRODUCT formula, so that's the only reason for the SUMPRODUCT. The INDEX works as normal.