# Finding nth occurrence of a value

#### rigreene

##### New Member
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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Thanks!

#### XOR LX

##### Well-known Member
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

##### New Member
Also great. Thanks Danerida and XOR LX!

#### Canapone

##### Active Member
Hi All,

may I ask which version of Excel are you using?

#### Canapone

##### Active Member
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.

Replies
4
Views
190
Replies
1
Views
320
Replies
1
Views
289
Replies
0
Views
154
Replies
5
Views
157

1,195,618
Messages
6,010,731
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back