Xlookup formula to return multiple results

MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi. I have a list of unique IDs (column A) with different dates in column B, C and D. If one of the dates are 1 year old, or older, I want the ID and the age (today() - date in column B, C, D) to be presented in a list in Column I (ID) and Column J (age of date), up to a maximum of 10 results. I currently have an xlookup that does the work for me, but xlookups cant really handle multiple results. I did some Googling and found that Index might help, but I have no knowledge of this formula and online resources aren't very helpful with explaining the Index function.
I have added a screenshot that shows what my formula looks like for cell I2 and J2.
 

Attachments

  • Capture.PNG
    Capture.PNG
    28 KB · Views: 12

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
The LET functions are worth taking a look at. You can take apart the formula by showing different variables at the end to see how the formula works.
MrExcelPlayground19.xlsx
ABCDEFGHIJKLMN
1IDDate1Date2Date3IDDate1IDDate2IDDate3
218/12/20238/12/20238/12/2023135911184811927
325/12/202310/1/20236/1/20222747075466879
431/1/202211/24/20239/7/2022364198832
548/12/202210/9/20235/28/2022443687826
658/26/20226/18/20237/7/2022512818
766/22/20229/10/20233/19/2021610782
875/2/20222/15/20225/11/202179758
988/16/20225/24/20235/5/2021813685
10910/31/20225/4/20237/18/202194444
111010/8/20223/24/20236/24/2021102440
121112/8/20224/19/20211/30/2021
13123/25/20234/3/20235/19/2021
14138/4/20238/15/20239/29/2021
15149/22/202312/25/202211/25/2022
16159/3/20231/1/20234/4/2023
Sheet29
Cell Formulas
RangeFormula
I2:J5I2=LET(a,A2:A16,b,B2:B16,c,TODAY()-b,d,HSTACK(a,c),dd,FILTER(d,c>365),e,TAKE(SORTBY(d,c,-1),10),f,FILTER(e,TAKE(e,,-1)>364),f)
K2:L3K2=LET(a,A2:A16,b,C2:C16,c,TODAY()-b,d,HSTACK(a,c),dd,FILTER(d,c>365),e,TAKE(SORTBY(d,c,-1),10),f,FILTER(e,TAKE(e,,-1)>364),f)
M2:N11M2=LET(a,A2:A16,b,D2:D16,c,TODAY()-b,d,HSTACK(a,c),dd,FILTER(d,c>365),e,TAKE(SORTBY(d,c,-1),10),f,FILTER(e,TAKE(e,,-1)>364),f)
Dynamic array formulas.
 
Upvote 0
Solution
Thank you very much. The more I work with Excel, the more I realize I don't know much about it lol. I'll have to study that formula a while to understand why it works, but for now the important part is, it works.
 
Upvote 0

Forum statistics

Threads
1,215,077
Messages
6,122,991
Members
449,094
Latest member
masterms

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
Back
Top