How to write this formula using =address

[caliray]

I’m using this formula in cell b1639 =COUNTIF($A$2:$A$3000,$A1639) to test for duplicates in the range a2:a3000. Currently no match returns a value of 1 and a match returns a value of 2.

Can the formula be revised using the address function as part of the formula to return a cell number of a match?

Maybe I am using the wrong function altogether. If so, I certainly will change it if someone can suggest a better option.

[PYS]

Hi

If I understand you want to get the cell reference of the duplicate? If that’s right, you can use:

• MATCH to find the row number of the duplicated string
• OFFSET to get the value of a cell offset by this number of rows
• CELL to get the address

You just pay attention to which cells need to be in absolute or relative references in the formulas.

See an example in Duplicates.ods

HTH

Regards

[erAck]

Rather use ADDRESS instead of CELL to get the address.