Cell Address and Values of Duplicates in Google sheet

Solution for Cell Address and Values of Duplicates in Google sheet
is Given Below:

In the following Dataset i am trying to get the Date corresponding to Enterprise Sales Singapore in Row 7. It depends on following condition:

  • “Offers given” has some value in latest Enterprise Sales Singapore

  • Find last duplicate which has some value in “offers given”.

  • Get the date of next Duplicate which has no value in “offers given”

My approach has been to find the cell address of last duplicate with some value in “Offers given” (B6) then run Vlookup in the range (B7:B10) to get the “Date” value of B7.
But i have been unable to get the cell address B6 instead i am getting B3.

enter image description here

Dataset

try:

=INDEX(ADDRESS(MAX(IF(
 COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A))=MAX(
 COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A))), ROW(A1:A), )), COLUMN(A1), 4))

enter image description here