if, and match
 

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.

if, and match
 

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.

if, and match
 

I would suspect some of your cells in column A are formatted as text.

Regards,
Paul

"SteveDB1" wrote in message
...
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.

if, and match
 

Row locations are my goal.

I've noticed that "match" finds the row that the requested value is located
in.

Ok, so that I understand this correctly....

Because I'm dropping one row each equation, it "resets" the starting point
to be that cell the equation is in?

While that kind of makes sense, in this case, I have some answers whose
answers should only be one or two row values off. Some of these are upwards
of 200 rows short. I.e., my errors are stating that a value is located in row
46, and it's actually in 157, and I'm near the top.

I just went back and looked more closely, it does appear that the "reset"
occurs; thus making that row's location the starting point.

I.e., if my equation is in row 11, then row eleven is my starting point for
my search the way it is written.

Ok, let me ask this.

how can I have it ignore the one row that I'm in with that specific equation?

I.e., =if(b12<1,match(a12, a1:a11;a13:a300,0),"ok")

Again, 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.

if, and match
 

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.

if, and match
 

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.

if, and match
 

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.