CODEWITHSUNDEEP
excelexcel-formula
cfardella
cfardella

Reputation: 89

Match date in column and work backwards to find first prior occurrence of value

I am trying to find a formula that matches a name in column 1 and then date in a dynamically selected column and then work backwards to find the date of the first prior occurrence of a specific value (in this instance RNR) from that date.

Data Table:

Name 01/07/2021 02/07/2021 03/07/2021 04/07/2021 05/07/2021 06/07/2021
Bob RNR RNR RNR DS DS DS
Joe RNR DS DS DS DS DS

For example, in the following table, if I specify Bob and 05/07/2021, then working backwards the first prior occurrence of RNR is 03/07/2021. For Joe it would be 01/07/2021.

Name Date Date of last RNR occurrence
Bob 05/07/2021 ??? (expected result is 03/07/2021)
Joe 04/07/2021 ??? (expected result is 01/07/2021)

Any help is appreciated

Upvotes: 0

Views: 876

Answers (2)

P.b
P.b

Reputation: 11578

=MAXIFS($1:$1,2:2,"RNR",$1:$1,"<"&B7) would work and copy down. This works if the names listed are in the same order as your data. Otherwise combine it with INDEX: =MAXIFS($1:$1,INDEX($1:$3,MATCH(A7,A1:A3,0),),"RNR",$1:$1,"<"&B7)

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36890

Give a try on below formula.

=MAX(($B$2:$G$3="RNR")*($A$2:$A$3=A7)*($B$1:$G$1))

You may need array entry for non 365 version of excel. Array entry means confirm formula to cell by pressing CTRL+SHIFT+ENTER.

enter image description here

Upvotes: 1

Related Questions