Find nth occurance of data
 

How to find out the nth occurance of the data in a table?
A B C
1 a 10
2 b 5
3 a 15
4 c 20
5 b 30
6 a 40
There are three'a' s in the table. I want to list out the value in the
C-column for the third occurance of 'a' = 40.
with regards
sreedhar

Find nth occurance of data
 

Try one of these:

This is an array formula** :

=INDEX(C1:C6,SMALL(IF(B1:B6="A",ROW(C1:C6)-MIN(ROW(C1:C6))+1),n))

Where n is the instance number you want.

The 3rd "A" in your list also happens to be the last "A" in the list. If you
want the *last* instance of "A":

Normally entered, not an array:

=LOOKUP(2,1/(B1:B6="A"),C1:C6)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
How to find out the nth occurance of the data in a table?
A B C
1 a 10
2 b 5
3 a 15
4 c 20
5 b 30
6 a 40
There are three'a' s in the table. I want to list out the value in the
C-column for the third occurance of 'a' = 40.
with regards
sreedhar

Find nth occurance of data
 

Thank you Biff. The formula works like a charm
With regards
Sreedhar

"T. Valko" wrote:

Try one of these:

This is an array formula** :

=INDEX(C1:C6,SMALL(IF(B1:B6="A",ROW(C1:C6)-MIN(ROW(C1:C6))+1),n))

Where n is the instance number you want.

The 3rd "A" in your list also happens to be the last "A" in the list. If you
want the *last* instance of "A":

Normally entered, not an array:

=LOOKUP(2,1/(B1:B6="A"),C1:C6)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
How to find out the nth occurance of the data in a table?
A B C
1 a 10
2 b 5
3 a 15
4 c 20
5 b 30
6 a 40
There are three'a' s in the table. I want to list out the value in the
C-column for the third occurance of 'a' = 40.
with regards
sreedhar

Find nth occurance of data
 

See the formulas in the "Arbitrary Lookups" section of
http://www.cpearson.com/Excel/TablesAndLookups.aspx .


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"yshridhar" wrote in message
...
How to find out the nth occurance of the data in a table?
A B C
1 a 10
2 b 5
3 a 15
4 c 20
5 b 30
6 a 40
There are three'a' s in the table. I want to list out the value in the
C-column for the third occurance of 'a' = 40.
with regards
sreedhar

Find nth occurance of data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
Thank you Biff. The formula works like a charm
With regards
Sreedhar

"T. Valko" wrote:

Try one of these:

This is an array formula** :

=INDEX(C1:C6,SMALL(IF(B1:B6="A",ROW(C1:C6)-MIN(ROW(C1:C6))+1),n))

Where n is the instance number you want.

The 3rd "A" in your list also happens to be the last "A" in the list. If
you
want the *last* instance of "A":

Normally entered, not an array:

=LOOKUP(2,1/(B1:B6="A"),C1:C6)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"yshridhar" wrote in message
...
How to find out the nth occurance of the data in a table?
A B C
1 a 10
2 b 5
3 a 15
4 c 20
5 b 30
6 a 40
There are three'a' s in the table. I want to list out the value in the
C-column for the third occurance of 'a' = 40.
with regards
sreedhar