Xlookup With Duplicates in Return Array

Aksaunders

New Member
Joined
Mar 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Attachments

  • EC708577-E1A2-445C-8BC1-D5BD2EEDA4A3.jpeg
    EC708577-E1A2-445C-8BC1-D5BD2EEDA4A3.jpeg
    177 KB · Views: 117

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,815
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
5Kaden4.3101/01/20214.3105/01/20214.3406/01/2021
Sheet4
Cell Formulas
RangeFormula
B5B5=SMALL($B$2:$G$2,1)
C5,E5,G5C5=AGGREGATE(15,6,$B$1:$G$1/($B$2:$G$2=B5),COUNTIF($B5:B5,B5))
D5D5=SMALL($B$2:$G$2,2)
F5F5=SMALL($B$2:$G$2,3)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
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.
 

Aksaunders

New Member
Joined
Mar 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFG
5Kaden4.3101/01/20214.3105/01/20214.3406/01/2021
Sheet4
Cell Formulas
RangeFormula
B5B5=SMALL($B$2:$G$2,1)
C5,E5,G5C5=AGGREGATE(15,6,$B$1:$G$1/($B$2:$G$2=B5),COUNTIF($B5:B5,B5))
D5D5=SMALL($B$2:$G$2,2)
F5F5=SMALL($B$2:$G$2,3)
Thank you, Jason. This works perfectly for me.
 

Aksaunders

New Member
Joined
Mar 21, 2021
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Forum statistics

Threads
1,170,959
Messages
5,872,995
Members
432,955
Latest member
minhnhat2504

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top