Ask Your Question

how to write this formula using =address [closed]

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-04 22:37:59.606380

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

Question Tools

1 follower


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

Seen: 106 times

Last updated: May 10 '16