Find Last Entry (Date) in a List satisfying a Condition

[rau]

My Data comprises a number of rows, and 2 columns. The data grows down the page over time. The First column contains a single Text character and the second column contains a date. The dates are not necessarily in order. I want to search down column 1 to find the row with the LAST "S" (noting there may be several rows with an S in col 1), and return the Date which is in that row in column 2.

Many thanks in advance.

[dilipandey]

Hi Rau,

Try using below formula:-

{=INDEX($B:$B,MAX(IF(ISNUMBER(SEARCH("s",$A:$A)),ROW($A:$A),"")),0)}

remember it need to be entered with Ctrl Shift Enter key combination.

See attached:-Extracting data in front of last S.xlsx

regards,
DILIPandey

<click on below 'star' if this helps>

[vlady]

maybe also

=MAX(INDEX(B:B*(A:A="s"),0),2)

***not really tested if all possibility will give correct values, maybe try it. thanks.

[rau]

THanks (Dilipandey) - almost exactly what I need. Could you show me how to modify the formula to constrain the vertical rows through which it searches. The scenario is that:
- I want to nominate (in the formula) the first row from which searching begins;
- I am writing the result into a row in the data table (col B)- so I want to stop searching in the row just above the 'current' row (where the formula resides).

Hope that's expressed clearly?

ALSO - note that in column A, each entry is only a SINGLE character, eg. "S", or "T" or etc.

[rau]

OK, maybe I've got it:

{=INDEX(B1:B10,MAX(IF(ISNUMBER(SEARCH("s",A1:A10)),ROW(A1:A10),"")),0)}

This formula is in cell B11. Thanks for your help.

[dilipandey]

Cheers

Please mark this thread as [solved].. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>