Find the nth Value in an Index(Match list

[jamesuk]

Hi All

hope you're all well?

Is it possible to have an index(match look at and retrieve the value of the nth occurrence in an array?

For example. if in Column A I have:

Cat
Dog
Cat
Monkey
Elephant

then in column B I have:

1
3
5
6
8

I want my index match to return the value of the 2nd occurence of Cat (so, 5), how could I do this please?

  =INDEX(A1:B5,MATCH("Cat",A1:A5,0),2)

would return 1


Many thanks in advance for your help!

 

[AlanY]

try this

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="Cat",ROW($A$1:$A$5),""),2),1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

If using Excel 2010 or later an option without the need for Ctrl+Shift+Enter

Excel Workbook
	A	B	C	D	E
1	Cat	1		Value	Cat
2	Dog	3		Occurence	2
3	Cat	5		Result	5
4	Monkey	6
5	Elephant	8
6
Match Nth

 

[jamesuk]

Hi AlanY

Thanks - I tried this and it does work which is brilliant!

If there was a 3rd Cat value, and I wanted that... would I just change the number '2' in the formula to 3?

 

[AlanY]

Hi AlanY

Thanks - I tried this and it does work which is brilliant!

If there was a 3rd Cat value, and I wanted that... would I just change the number '2' in the formula to 3?

yes, exactly

 

[jamesuk]

Hi Both

Thank you both for your help they both worked great.

But I decided to go with Peter's suggestion (as i'm using 2010)

edited a little as I realised I needed it to take the value in the cell below the occurrence in the same column

=INDEX(A:A,(AGGREGATE(15,6,(ROW(A:A)-ROW(A1)+1)/(A:A="Cat"),COUNTIF(A:A,"Cat"))+1))

Thank you again for your help!

 

[Peter_SSs]

... (as i'm using 2010)

edited a little as I realised I needed it to take the value in the cell below the occurrence in the same column

=INDEX(A:A,(AGGREGATE(15,6,(ROW(A:A)-ROW(A1)+1)/(A:A="Cat"),COUNTIF(A:A,"Cat"))+1))

Especially since you are using a recent version of Excel, it is best not to use whole column references if you don't need to. Why check over 1,000,000 rows if you are using, say, only a few hundred?

Also, it appears from your formula that you are not trying to find the Nth occurrence of 'Cat' but rather the last occurrence. In that case you can change the first AGGREGATE argument to 14 (LARGE) & then you don't need the COUNTIF.

For example,

=INDEX(A1:A1000,(AGGREGATE(14,6,(ROW(A1:A1000)-ROW(A1)+1)/(A1:A1000="Cat"),1)+1))

You can change the 1000 rows if you need to but this calculates over 100 times faster than the formula you posted.

 

[jamesuk]

Hi Peter

Quite right!

believe it or not the range of data i'm looking at can go over 500,000 rows

Im sure i'll put a specific array in when I'm finished making what i'm making, so i'll keep that in mind

 

[billandrew]

I was looking at this post, very interesting. How could I amend to use this for numeric values.

 

[Peter_SSs]

How could I amend to use this for numeric values.

In what way does it not already work for numbers as is?
Are you talking about the original question of finding the Nth occurrence, or the subsequent part about finding the last occurrence, or even finding the value below the last occurrence like in posts #6 & 7?
What are your particular circumstances?