MATCH with a running lookup_value

Question

I have data as follows:

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.

What do you want to "happen" on the sheet when that condition is met? Conditional formatting? You're wanting to return one or all of the values? — ashleedawg, Feb 19 '18 at 12:10
No, I have another cell in which I return the retirement age, just like the one in the accepted answer. — Rachcha, Feb 19 '18 at 13:48

score 4 · Answer 1 · answered Feb 19 '18 at 12:23

4

You could also use Aggregate to find the smallest age for which row 3 is greater than row 2

=AGGREGATE(15,6,B1:G1/(B3:G3>B2:G2),1)

answered Feb 19 '18 at 12:23

Tom Sharpe

30,727
5
24
37

I was thinking about something using `AGGREGATE`, but opted for a solution with the original `MATCH` function :) +1 for a nice alternative! – CallumDA Feb 19 '18 at 12:24
Cheers! +1 for yours obvs. – Tom Sharpe Feb 19 '18 at 12:31

CallumDA · Accepted Answer · 2018-02-19T13:48:18.173

Try this:

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

it returns the following, as required:

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.

MATCH with a running lookup_value

2 Answers2