# Xlookup With Duplicates in Return Array

#### Aksaunders

##### New Member
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
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
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
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)

#### Fluff

##### MrExcel MVP, Moderator
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.

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
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
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
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.

Replies
6
Views
128
Replies
4
Views
309
Replies
2
Views
117
Replies
2
Views
1K
Replies
3
Views
328

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.

### Which adblocker are you using?

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

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