index match second result

[j4ymf]

Good Evening

I was wondering would it be possible to find the first and second result.

My look up is joe bloggs and as you can see from my image my first result is Apples and the second result is Pears
How would i do this

thank you in advance, Jason

 

[j4ymf]

=IFERROR(INDEX(Download!$D$1:$D$9999,AGGREGATE(15,6,(ROW(Download!$D$1:$D$9999)-ROW(Download!$D$1)+1)/(Download!$F$1:$F$9999=B8),1)),"")

I was given this code on the forum last week which finds my first result its just the second result im struggling with

Thank you

 

[Eric W]

Try:

Book5
	A	B	C	D	E	F	G	H
1
2
3		Joe Bloggs	Apples	First result
4		Joe Bloggs	Apples				Lookup	Joe Bloggs
5		Joe Bloggs	Apples				First result	Apples
6		Joe Bloggs	Apples				Second Result	Pears
7		Joe Bloggs	Apples
8		Joe Bloggs	Apples
9		Joe Bloggs	Apples
10		Joe Bloggs	Apples
11		Joe Bloggs	Pears	Second Result
12		Joe Bloggs	Pears
13		Joe Bloggs	Pears
14		Joe Bloggs	Pears
15		Joe Bloggs	Pears
16		Joe Bloggs	Pears
17
Sheet17

Cell Formulas
Range	Formula
H5:H6	H5	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$16)/(COUNTIF(H$4:H4,$C$3:$C$16)=0)/($B$3:$B$16=H$4),1)),"")

 

[j4ymf]

Thank you Eric
What formula do i put in H6 to get my second result?

 

[j4ymf]

Hi Eric,
I think ive got it
Thank you for your help

ill just have a play

 

[j4ymf]

Hi Eric
Thats works fantastic, Thank you

 

[Eric W]

Happy to help! You can continue to drag the formula down further to get additional results.