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!

 

[dcprop]

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

Match Nth

*	A	B	C	D	E
Cat	*
Dog	*
Cat	*
Monkey	*	*	*
Elephant	*	*	*
*	*	*	*	*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]

[TD="align: right"]1
[/TD]

[TD="align: right"]Value
[/TD]
[TD="align: center"]Cat
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]

[TD="align: right"]3
[/TD]

[TD="align: right"]Occurence
[/TD]
[TD="align: center"]2
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]

[TD="align: right"]5
[/TD]

[TD="align: right"]Result
[/TD]
[TD="align: center"]5
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]

[TD="align: right"]6
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]

[TD="align: right"]8
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E3 =INDEX($B$1:$B$5,AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/($A$1:$A$5=E1),E2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter,

Would you be able to explain how exactly this is working? I understand the aggregate is using the small function (only ignoring errors), but how else is everything working? The Row()-Row()+1/A1:A5=E1 portion is the part I'm struggling with.

Also, is it possible to expand the number of exclusions? For example, if A6 = Cat and B6 = 20, would I be able to run the same function but return the 2nd occurrence excluding anything in column B that = 5? This would make the result = 20

 

[Peter_SSs]

Hi Peter,

Would you be able to explain how exactly this is working? I understand the aggregate is using the small function (only ignoring errors), but how else is everything working? The Row()-Row()+1/A1:A5=E1 portion is the part I'm struggling with.

Also, is it possible to expand the number of exclusions? For example, if A6 = Cat and B6 = 20, would I be able to run the same function but return the 2nd occurrence excluding anything in column B that = 5? This would make the result = 20

(ROW($B$1:$B$5)-ROW($B$1)+1) is used to construct an array of the relative row numbers of the range to be indexed. In the example referred to {1,2,3,4,5}
The reason that construct is used and not simply ROW($B$1:$B$5) [which would also produce the array {1,2,3,4,5}] is to protect the integrity of the formula if rows are subsequently inserted at the top of the sheet.
If one row was inserted, the range to be indexed would now be B2:B6. The first construct will still produce the array {1,2,3,4,5}, which is what we want to refer to one of the cells in the range, whereas the second construct would now return {2,3,4,5,6} which would produce incorrect results or an error.

To ensure multiple conditions are met the extension of such a formula is
=INDEX(range,15,6,row_array/((Condition 1) * (Condition 2) * (Condition 3) ....),N)
If any of the conditions are false, the denominator of the division will be zero, producing an error and caus that row to be ignored.

So, for your example above you could use ..

Excel Workbook
	A	B	C	D
1	Cat	1		20
2	Dog	3
3	Cat	5
4	Monkey	6
5	Elephant	8
6	Cat	20
Match Nth

 

[dcprop]

This is amazing and very helpful! Thanks!

 

[Peter_SSs]

This is amazing and very helpful! Thanks!

You are welcome.