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.