CODEWITHSUNDEEP
excelfunctionexcel-formulamatch
achancyatelier
achancyatelier

Reputation: 1

Return correct row from a particular set of rows within the range

I have below sample extract of movement of deals from the CRM. I use the formula: =MAX(INDEX(($A$2:$A$21=1)*($D$2:$D$21<$P$2)*$D$2:$D$21,)), where P2 is a date, to extract the last movement of deal "1" prior to the date in P2 (currently set to 01/10/2020). I get 12/09/2020 which is correct.

From here, I will need to return its particular row number and I try to use the MATCH formula: MATCH(MAX(INDEX(($A$2:$A$21=1)*($D$2:$D$21<$P$2)*$D$2:$D$21,)),$D$2:$D$21,0). However, since the lookup array ($D$2:$D$21) is only looking up the date and doesn't consider for which deal it belongs, it doesn't always return the correct row number.

I need the lookup array to be narrowed down to the rows that belong to the particular deal ID. I also need the MATCH formula to return the last occurrence of the date in case there are several of them that happens within the same day (see Co C_Deal 1). Is there a more accurate way to do this?

I really appreciate any assistance and already thank you in advance for your help. Thanks thanks!

Deal ID Co Name Deal Date Stage History
1 Co A Co A_Deal 1 02/02/2020 Lead Qual
1 Co A Co A_Deal 1 23/06/2020 Demo
1 Co A Co A_Deal 1 12/09/2020 Proposal
1 Co A Co A_Deal 1 01/10/2020 Contracts
1 Co A Co A_Deal 1 31/12/2020 Won
2 Co B Co B_Deal 1 16/01/2020 Lead Qual
2 Co B Co B_Deal 1 14/03/2020 Proposal
2 Co B Co B_Deal 1 15/03/2020 Demo
2 Co B Co B_Deal 1 30/07/2020 Proposal
2 Co B Co B_Deal 1 09/10/2020 Contract
2 Co B Co B_Deal 1 06/11/2020 Won
3 Co C Co C_Deal 1 16/09/2020 Demo
3 Co C Co C_Deal 1 16/09/2020 Lead Qual
3 Co C Co C_Deal 1 10/10/2020 Proposal
4 Co D Co D_Deal 1 03/04/2020 Lead Qual
4 Co D Co D_Deal 1 06/05/2020 Proposal
4 Co D Co D_Deal 1 13/05/2020 Demo
4 Co D Co D_Deal 1 12/09/2020 Proposal
4 Co D Co D_Deal 1 15/10/2020 Contracts
4 Co D Co D_Deal 1 28/11/2020 Won

Upvotes: 0

Views: 61

Answers (1)

Pavol Velky
Pavol Velky

Reputation: 810

Here is the solution:

=MAX(IF(ISNUMBER(MATCH(INDEX(($D$2:$D$21<$H$2)*($A$2:$A$21)),$H$1,0)),ROW($D$2:$D$21),""))

Enter it as array formula. $H$1 is cell with dealer ID. Formula performs search for every occurrence that satisfies date and dealer ID condition and return maximal row number.

Upvotes: 1

Related Questions