Search for third instance

[30percent]

Hi,

=INDEX('Sheet1'!$A:$A,MATCH(TRIM($B28),'Sheet1'!$A:$A,0)+4)

I have the following formula that look up value in cell B28 in 'Sheet1'!$A:$A and return value from the cell 4 rows below that. Say value in B28 is "test". How do I change my formula so that, it would return value from the cell 4 rows below not the first instance but the 3rd instance of "test"?

thanks.

 

[Eric W]

Try:

Book2
	A	B	C
1	List
2	test
3	a
4	b
5	c
6	d
7	e
8	f
9	test
10	g
11	h
12	i
13	j
14	k
15	l
16	m
17	n
18	test
19	o
20	p
21	q
22	r
23	s
24	t
25	u
26	test
27	v
28	w	test	r
29	x
30	y
31	z
Sheet1

Cell Formulas
Range	Formula
C28	C28	=INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A:$A)/(Sheet1!$A:$A=TRIM($B28)),3)+4)

 

[myall_blues]

Another option:

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$A:$A=$B$28,ROW(Sheet1!$A:$A),""),3)+4),"")

 

[myall_blues]

Neglected to add formula above is an array formula and needs to be entered with CTRL+SHIFT+ENTER