Ok
I've got it now-- thank you.
The only difference is that the rows function needs to be row() instead of
rows(A$1:A1)
Having the row() gives the actual row number, and the rows(A$1:A1) offsets
it by a negative one-- making me think it'd read rows(A$1:A1) as 0, then 1,
2, ......, n
So, the function then becomes
=IF((B11)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$30 0,0)+ROW(),"")
ballissimo.....
Thank you very much for your help.
Enjoy your weekend.



"JMB" wrote:

You could try:
=IF((B11)*(COUNTIF(A$1:A1,A1)=1),MATCH(A1,A2:A$30 0,0)+ROWS(A$1:A1),"")

which would tell you where the second occurence is (I assume there can't be
more because you said the countif function will return either 1 or 2).

If you are trying to identify the duplicates, you could enter
=Countif(a$1:a1)
in cell B1, copy down to B300. Then autofilter on column B (may want to
insert a header row) using a custom criteria of greater than 1. That would
bunch all of them together for you and you could delete the filtered rows
(Edit/Delete Row) - if your intention is to delete them.

Just be sure to backup your data before trying new things.


"SteveDB1" wrote:

Ok, While I've solved my problem in three steps as opposed to one, I'd still
like to know if I can use match and skip the row that my equation is located
in. I.e., a jump over as it were.
Is that possible, and if so, how?
Thank you.


"JMB" wrote:

The position is relative to the range given to the match function. Are you
trying to find the row number the second value is in or the index number
within the range containing your data (which appears to start at A1, so these
two are probably the same - for now). If the formula you posted returns a 3,
that means it is in cell A4 (third cell in the range A2:A300). You could
perhaps try:

=if(b1<1,match(a1,a2:a$300,0)+rows(a$1:a1),"ok")

I assume your data is sorted and you are trying to find the row number.

"SteveDB1" wrote:

Hello all.
Ok, I have a question about my formula in excel.

=if(b1<1,match(a1,a2:a$300,0),"ok")

b1 is a cell that I've used countif in. In this specific case, I'll either
have 1, or 2 as my values.

What I want to do is to locate the whereabouts of the second value that
matches a1, somewhere in column a.
This is why I started at the next cell down from my source cell--a1.
I.e., I am already aware of the first appearance of the number, I want
subsequent appearances, and locations.
The problem that I've gotten is that it's not showing the correct cell
location, and appears to be some 5 to 140 cells off from the actual location.
Here's my understanding of my question.

if the b1 cell's value does not equal 1, look throughout the chosen
range--a2 through a300-- for the specified cell--a1--value, if it's found,
tell me where it is; else if b1 does equal 1, everything is ok.

I.e., the matched value shows the location as 168, and is actually in 176 (I
used ctrl+f), another one states by match value that it's in 46, and by
ctrl+f, I found it in cell 157.
I'm not able to readily identify anything that I've done incorrectly here,
so a second, or nth pair of eyes would be appreciated.

As always, thank you, in advance.