CODEWITHSUNDEEP
excelexcel-formulaexcel-match
Rachcha
Rachcha

Reputation: 8816

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.

Upvotes: 1

Views: 67

Answers (2)

CallumDA
CallumDA

Reputation: 12113

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.

Upvotes: 3

Tom Sharpe
Tom Sharpe

Reputation: 34180

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)

Upvotes: 4

Related Questions