Find a date and its row number in a column of dates

I have dates sorted in ascending order in a column. I want to find a date and its absolute row number for two different cases.

Case 1 (Formula 1 & Formula 2): I want to find the first date (and its row number) that is equal to a given date or the next available date if it does not exist

Case 2 (Formula 3 & Formula 4): I want to find the last date (and its row number) that is equal to a given date or the last previous date if it does not exist.

For example, I have a column with dates:
row1: 8/1
row2: 8/2
row3: 8/2
row4: 8/4
row5: 8/4
row6: 8/5
row7: 8/7


Case 1:
Given Date= 8/3 => Excel should find 8/4 and row4 (show results in 2 different cells)
Given Date= 8/4 => Excel should find 8/4 and row4 (show results in 2 different cells)

Case 2:
Given Date= 8/3 => Excel should find 8/2 and row3 (show results in 2 different cells)
Given Date= 8/4 => Excel should find 8/4 and row5 (show results in 2 different cells)

Thank you so much in advance!

p.s.
Case 1 is used as the start date of a calculation
Case 2 is used as the end date of a calculation

Is this a homework assignment?

I have uploaded a workbook with my Case 2 formulas. However, I can't figure out the Case 1 formulas to use in Cells F16 and G16.

p.s. I have subscribed to this post. However, I am not notified by E-mail when there is a response. I am also not notifed about private messages (I have confirmed the E-mail and it is not going to Spam)

There are some issues for some people with subscriptions - apologies for that. The tech team are aware.

Try this:

=MIN(LOOKUP(2,1/($A$2:$A$8<=A17),ROW($A$2:$A$8)),IFNA(MATCH(A17,$A$2:$A$8,0),99^99))

Why have you removed the workbook you provided???

I am attaching it here with my solution added for the benefit of everyone reading this thread.

EDIT: Please don't post-edit workbooks! If you have made a mistake, add an amended workbook in a new post to the thread.

Amended for the new file:

=MIN(LOOKUP(2,1/($A$1:$A$7<=A16),ROW($A$1:$A$7))+1,IFNA(MATCH(A16,$A$1:$A$7,0),99^99))

Some response to my attempts to help you would be appreciated!

I've given a formula for the new workbook above. Sorry - I did not spot Excel 2010. Give me a moment to amend again.

Try this:

=MIN(LOOKUP(2,1/($A$1:$A$7<=A16),ROW($A$1:$A$7))+1,IFERROR(MATCH(A16,$A$1:$A$7,0),99^99))

In future, please don't post-edit posts and attachments - as has been proved here, it just leads to confusion. Correct mistakes simply by adding a new post to the thread. Thanks.

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

Thanks for the new formula! It works in the workbook that I uploaded, with Dates in A1:A7.
However, it strangely doesn't work when the dates are in A3:A9 (my simpler Case 2 Formulas work in either case).

I have attached two workbooks with your formula and the dates in either A1:A7 or A3:A9.

How do I modify your formula to work for a different range of dates other than A1:A7, e.g. A3:A9 (in addition to, of course, changing the range from A1:A7 to A3:A9 in the formula)?

p.s. I have changed the wording from "row" to "position" as everything is in relation to the position in the array (which is needed for the INDEX function to work)

Try this:

=MIN(LOOKUP(2,1/($A$3:$A$9<=A18),ROW($A$1:$A$9))+1,IFERROR(MATCH(A18,$A$3:$A$9,0),99^99))

The bit in red is where you went wrong. This needs to start at A1 to give the position in the array that you want.

If this is now what you want, please mark the thread as solved.

If you are still puzzling over it tomorrow and want me to explain, just holler (I think that’s what you Americans say).

Yes, most of what I know has been learnt since joining this forum.