Ask Your Question

how to write this formula using =address

asked 2016-05-06 00:53:27 +0100

caliray gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-05-10 16:25:33 +0100

pierre-yves samyn gravatar image


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



edit flag offensive delete link more


Rather use ADDRESS instead of CELL to get the address.

erAck gravatar imageerAck ( 2016-05-10 16:54:23 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-05-06 00:53:27 +0100

Seen: 56 times

Last updated: May 10 '16