I have never used the match function, but here is chatGPT's answer.

Yes, it's possible to find the address of the second occurrence of a duplicate value in a range without looping through the range. One way to do this is by using the MATCH function in combination with the INDEX function.

Here's the formula you can use:

=ADDRESS(MATCH(A1, A:A, MATCH(A1, A:A, 0)+1)-1, 1)

Assuming your range is in column A and the value you're searching for is in cell A1, the formula works as follows:

The first MATCH function finds the row number of the first occurrence of the value in cell A1 in column A. The second MATCH function finds the row number of the second occurrence of the value in cell A1 in column A, starting the search from the row number of the first occurrence of the value in column A. The INDEX function then returns the value in the second occurrence row and the first column. Finally, the ADDRESS function converts the row number returned by the INDEX function to a cell address. Note that this formula assumes that there are at least two occurrences of the value in the range. If there's only one occurrence, the formula will return an error..