find nth occurence

[GinoZ]

Hey guys,

i have an array with the following layout

A       3
B       3 
V       2 
F       1 
G       2 
H       4

I want to find every 2 in the array. When it is found, i need to display the letter. Example:
V
G

The array DOES contain blank rows
Thanks in advance

GinoZ

 

[stanleydgromjr]

GinoZ,

You could use Data, Filter.

Or, you could follow this link for another solution:

[url]http://www.mrexcel.com/board2/viewtopic.php?t=285751&highlight=
[/url]

Have a great day,
Stan

 

[stanleydgromjr]

GinoZ,

VLOOKUP HLOOKUP to return multiple results.xls
	A	B	C	D	E	F	G	H	I	J	K
1	A	3					NumberofInterest
2	B	3					2	V		H2	=IF(ROWS(H$2:H2)>$G$5,"",INDEX($A$1:$B$9,SMALL(IF($A$1:$B$9=$G$2,ROW($A$1:$B$9)),ROW(1:1)),COLUMNS($A2:A2)))
3	V	2						G
4	F	1					No.ofRows
5	G	2					2
6	H	4
7
8
9
10
11							G5	=COUNTIF(B1:B9,G2)
12
Sheet2

The formula in cell G5:
=COUNTIF(B1:B9,G2)

The formula in cell H2:
=IF(ROWS(H$2:H2)>$G$5,"",INDEX($A$1:$B$9,SMALL(IF($A$1:$B$9=$G$2,ROW($A$1:$B$9)),ROW(1:1)),COLUMNS($A2:A2)))

When you paste the formula into cell H2, you want to confirm it with CTRL + SHIFT + Enter (because this is an array formula.

Copy H2 down to H9.


Make canges to cell G2.

Have a great day,
Stan