Xlookup With Duplicates in Return Array

[Aksaunders]

I’m tracking athletes top run times. Need to get top 3 times (including duplicates) and the associated dates. Dates are in row 1. Times start in row 2. I use SMALL to get best times. When using XLOOKUP to return the date, if a time is duplicated the formula pulls the date associated with the first instance of the time. I need the formula to pull the date in row 1 in the same column as the duplicate time. In the attached image, I’d like the formula in the cell with red border to return 1/5/21.

 

[jasonb75]

When using XLOOKUP to return the date, if a time is duplicated the formula pulls the date associated with the first instance of the time.

The same applies to any of the lookup or match functions, then will only return the first (or last depending on the exact function and parameters used). For anything else, you need to use other functions. This is probably the easiest way to do what you want.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
5	Kaden	4.31	01/01/2021	4.31	05/01/2021	4.34	06/01/2021
Sheet4

Cell Formulas
Range	Formula
B5	B5	=SMALL($B$2:$G$2,1)
C5,E5,G5	C5	=AGGREGATE(15,6,$B$1:$G$1/($B$2:$G$2=B5),COUNTIF($B5:B5,B5))
D5	D5	=SMALL($B$2:$G$2,2)
F5	F5	=SMALL($B$2:$G$2,3)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Xlookup with duplicate values in return array - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Also posted here Finding Duplicate Values with Xlookup

 

[Aksaunders]

The same applies to any of the lookup or match functions, then will only return the first (or last depending on the exact function and parameters used). For anything else, you need to use other functions. This is probably the easiest way to do what you want.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
5	Kaden	4.31	01/01/2021	4.31	05/01/2021	4.34	06/01/2021
Sheet4

Cell Formulas
Range	Formula
B5	B5	=SMALL($B$2:$G$2,1)
C5,E5,G5	C5	=AGGREGATE(15,6,$B$1:$G$1/($B$2:$G$2=B5),COUNTIF($B5:B5,B5))
D5	D5	=SMALL($B$2:$G$2,2)
F5	F5	=SMALL($B$2:$G$2,3)

Thank you, Jason. This works perfectly for me.

 

[Aksaunders]

Folks, I apologize for cross-posting this without informing the forum. I never thought about why this is such an annoyance for you guys who volunteer your time to help people like me. Again, I’m very sorry.