get 2nd occurence value

Match function fetches row number of 1st occurence of a value in a range,How 
to get 2nd or 3rd or later occurences of that value in a range?
0
9/29/2005 6:28:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
255 Views

Similar Articles

[PageSpeed] 2

Assuming that A1:A100 contains your data, and that B1 contains your 
lookup value, try the following for the second occurrence...

=SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.  For the third 
occurrence, change the 2 at the end of the formula to 3.

Hope this helps!

In article <6FC05ACF-01DA-43C1-B3AF-252211D1BF47@microsoft.com>,
 TUNGANA KURMA RAJU <TUNGANAKURMARAJU@discussions.microsoft.com> wrote:

> Match function fetches row number of 1st occurence of a value in a range,How 
> to get 2nd or 3rd or later occurences of that value in a range?
0
domenic22 (716)
9/29/2005 7:07:24 PM
Hello DOMENIC,
Thanks.you have done it.It helped me to solve a problem.With regards

"Domenic" wrote:

> Assuming that A1:A100 contains your data, and that B1 contains your 
> lookup value, try the following for the second occurrence...
> 
> =SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2)
> 
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.  For the third 
> occurrence, change the 2 at the end of the formula to 3.
> 
> Hope this helps!
> 
> In article <6FC05ACF-01DA-43C1-B3AF-252211D1BF47@microsoft.com>,
>  TUNGANA KURMA RAJU <TUNGANAKURMARAJU@discussions.microsoft.com> wrote:
> 
> > Match function fetches row number of 1st occurence of a value in a range,How 
> > to get 2nd or 3rd or later occurences of that value in a range?
> 
0
10/1/2005 7:00:01 AM
Reply:

Similar Artilces: