Marking the latest date in a set with duplicates in a column

[provanities]

Hi everyone! I've been banging my head against the wall trying to find a solution for this problem.

I have a dataset with prospects and stages.

I would like to have a marker on whether to include a row in the dataset based on an input date, but the issue is that several prospects have several dates and stages.

I've attached a doc that shows the types of data and where I'd like to have a binary output, with two examples and an edge case where there are duplicate dates. In this file, the rightmost column of each example shows which row should be returning a 0 or a 1, based on different dates entered above.

I would love, love, love any possible help.

[63falcondude]

Hello and welcome to the forum. Try this in H10:

=--(MAX(IF((F$10:F$37=F10)*(G$10:G$37<H$7),G$10:G$37))=G10) Ctrl Shift Enter

[63falcondude]

Missed the duplicates part...

This should work:

H10 =IFNA(--(ROW()=LOOKUP(2,1/((F$10:F$37=F10)*(G$10:G$37<H$7)),ROW(G$10:G$37))),0)

[provanities]

This worked! Amazing! You saved me so much time; thanks so much.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[63falcondude]

My pleasure, thanks for the rep!