Lookup to return nth value

[tinderbox22]

I'm trying to figure out how to lookup values in a small table where there may be more than one result that matches the lookup value.

In cells A1:B10, assume numbers in A1:A10 and names in B1:B10. I want to look up the numbers to return the names. If A5 and A8, for example, are the same number, a simple XLOOKUP won't work. How do I go about returning the next result instead of the first result twice?

 

[KRice]

Do you want to return all of the matching results or just a particular one? You’ve mentioned the nth value. Where is n?
Which version of Excel are you using?

 

[KRice]

One approach:

Book2
	A	B	C	D	E
1	2	a	match	n	Result
2	6	b	5	2	h
3	1	c	5	3	i
4	7	d	7	2	j
5	3	e	7	3	not found
6	5	f
7	9	g
8	5	h
9	5	i
10	7	j
Sheet3

Cell Formulas
Range	Formula
E2:E5	E2	=IFERROR(INDEX(FILTER($B$1:$B$10,$A$1:$A$10=C2),D2),"not found")

 

[tinderbox22]

Do you want to return all of the matching results or just a particular one? You’ve mentioned the nth value. Where is n?
Which version of Excel are you using?

I’d like to return all matching values. So first, second, third if it’s there, etc. using 365

 

[Peter_SSs]

using 365

Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you are after?

24 03 24.xlsm
	A	B	C	D
1	1	Name1		2
2	5	Name2		Name3
3	2	Name3		Name5
4	3	Name4		Name7
5	2	Name5		Name10
6	3	Name6
7	2	Name7
8	1	Name8
9	5	Name9
10	2	Name10
Lookup

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(B1:B10,A1:A10=D1,"")
Dynamic array formulas.

 

[tinderbox22]

Getting closer. I believe I figured it out. I'll just use the SORTBY function rather than a lookup.
In your example, instead of D1=2, D1:D10 = 1,2,3...10. My formula would go in E1:E10, using D1:D10 as lookup values. So essentially, I'm ranking the results. I got tripped up on the 'lookup' but it's really just a ranking/sorting function. Thank you all for your quick responses!