Finding nth occurrence of a value

[rigreene]

I want to be able to find the nth value using VLOOKUP, INDEX, or something similar.

Example:

=VLOOKUP(2,A1:B5,2,0)
would return "Blue"

	A	B
1	3	Green
2	2	Blue
3	4	Green
4	2	Red
5	5	Red

<tbody>
</tbody>

But, if I want it to find the 2nd "2" so that it returns "Red," how would I go about that?

Thanks!

 

[Danerida]

Excel Lookup nth Occurrence/Instance

Dan

 

[rigreene]

Thanks!

 

[XOR LX]

Hi.

I notice that link appears to be a UDF solution. If you want a purely formula-based solution, use this array formula**:

=INDEX(B1:B5,SMALL(IF(A1:A5=C1,ROW(A1:A5)-MIN(ROW(A1:A5))+1),k))

where C1 holds the search value, e.g. 2, and k at the end should be replaced with a number representing which occurrence of that search value you wish to return, e.g. 1=1st, 2=2nd, etc.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[rigreene]

Also great. Thanks Danerida and XOR LX!

 

[Canapone]

Hi All,

may I ask which version of Excel are you using?

 

[Canapone]

Hi,

if you are using Excel 2010 (or newer versions ) you could give a chance to a standard formula

=IFERROR(INDEX($B$1:$B$5,AGGREGATE(15,6,ROW($A$1:$A$5)/($A$1:$A$5=2),ROW(A1))),"")

copying down the formula you get all the occurences

If it's of any help.