Find Last Occurrence of Current Cell's Offset Row Value

[stusic]

Title sounds a bit weird, but it's really pretty simple (yet I'm posting a question about it, so apparently it's not that simple to me).

I'm looking for a formula that will get the value of a cell in the current row (say, C16) and find the last occurrence of that value in that column (C), only above the current row. Extra points for returning the value in column G of the last occurrence row.

I'm basically trying to find the last time a name (C16) occurred in a list (Column C) and see what weekday it was (column G).

I appreciate the help :beer:

[Ron Coderre]

Sounds a bit complicated. Can you post some sample data and the results you want to see...based on that sample data?

[stusic]

Sounds a bit complicated. Can you post some sample data and the results you want to see...based on that sample data?

Sure!

I posted the file here, as well as attached a screenshot of what I'm working with. You'll see each person works the same day (Saturday or Sunday), but should alternate weekend days (one Saturday, then one Sunday, one Saturday, one Sunday, etc). I'm trying to see the last day that an person worked - if it was a Saturday, then I know they need to work on a Sunday this week.

In the example, to see which day Engineer 1 should work, my formula needs to look at the date (actually taken from the date in column G, not the highlighted day of column F) and see which number engineer is in column C of the same row, then search UP to find the last time they worked and which day it was. Then I can change the day appropriately.

I hope this makes sense...

Thanks for any help



Excel Example.jpg

[bebo021999]

There may be 2 kind of sort:

1) Keep the Eng number sorted, then change the day, like this:

2) Keep the days sorted, then change the Eng number, like this:

As you said, maybe you refer to option 1, right?
Anyway, as per your solution

my formula needs to look at the date (actually taken from the date in column G, not the highlighted day of column F) and see which number engineer is in column C of the same row, then search UP to find the last time they worked and which day it was. Then I can change the day appropriately.

that look at day, then come back to change day, it is impossible.

I refer to option 2.
Is it accepted?

[stusic]

There may be 2 kind of sort:

1) Keep the Eng number sorted, then change the day, like this:

Yes, this way.

that look at day, then come back to change day, it is impossible.

I don't need to look at the day of the current row, I need to find the day of the last time the engineer worked.

[bebo021999]

I see. Follow below steps:
* G2: date input manually
* G3: =G2+IF(WEEKDAY(G2)=7,1,6)
Drag down to last person of first set of dates (G7)
* G8:
=LOOKUP(2,1/($E$2:E7=E8),$G$2:G7)+7*(MAX($C$2:$C$43)/2)+IF(WEEKDAY(LOOKUP(2,1/($E$2:E7=E8),$G$2:G7))=1,-1,1)
Drag down.