List of Dates with Duplicate Values

[MrDB4Excel]

All figures in this Xl2bb Mini Sheet are bogus and only shown to give value to the sheet.
To carry on from my posted thread yesterday, 4 December 2022, I am attempting to create a list of dates, which have the same count number, and yet are relevant to a specific number in the Main Rate column.
In other words, with cell O8 showing the number 2, and with cell C8 showing 21.578, then the sought-after formula would provide a list of the two dates associated with the rate of 21.578.
Similarly, with cell O12 showing the number 3, and with cell C12 showing 23.763, then the sought-after formula would provide a list of the two dates associated with the rate of 23.763.
This Xl2bb Mini Sheet is but a fraction example of another sheet, which will be updated when I learn of some, sought-after formula that will do what I have been searching about.
It may not be necessary to pay any attention to the formulas you find in the Xl2bb Mini Sheet because those are my feeble attempts and may not be what is needed anyway.
The column titled “Lookup Number” is only a list of possible duplicate counts that could exist in the much larger Excel file from which these examples have been taken.
And, if this is not enough then is it possible to infill dates into the relevant cells in the column titled “Extra Dates Per Specific Rate” or is this a difficult venture due to the fact there will be more than one rate that has one or more duplicates yet simultaneously different dates. In other words, the rate 21.688, of which there are two, have dates different than the dates shown for the rate 21.578.
Please help and if something isn’t clear let me know and I will do my best to clarify.
Any help is much appreciated.

ReturnDatesMatchCells.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Time	Date	Main Rate	Support Rate					Extra Dates Per Specific Rate	21	23	23	D Col Count	COUNT	Duplicate Count					Lookup Number
2					Min ððð	62,341.30		2,900				$B$23	21
3					Max ððð	68,912.70		3,100
4	9:26:09	Fri-01 April 2022	21.574	21.578	62,564.60		1	2,900						1	1	Wed-06 April 2022		Fri-01 April 2022	21.574	2	Fri-01 April 2022
5				0.004			0													3
6	17:47:32	Mon-04 April 2022	21.497	21.501	62,341.30		1	2,900						1	1				21.497	4
7				0.004			0													5
8	8:11:17	Wed-06 April 2022	21.578	21.582	62,576.20		1	2,900						2	2				21.578
9				0.004			0
10	8:06:34	Thu-07 April 2022	21.578	21.582	62,576.20		1	2,900						2	2				21.578
11				0.004			0
12	12:24:17	Fri-25 November 2022	23.763	23.767	68,912.70		1	2,900						3	3				23.763
13				0.004			0
14	9:19:06	Sat-26 November 2022	23.763	23.767	68,912.70		1	2,900						3	3				23.763
15				0.004			0
16	7:21:11	Sun-27 November 2022	23.763	23.767	68,912.70		1	2,900						3	3				23.763
17				0.004			0
18	15:05:29	Fri-07 April 2023	21.679	21.684	67,204.90		1	3,100						1	1				21.679
19				0.005			0
20	8:29:10	Sat-08 April 2023	21.688	21.693	67,232.80		1	3,100						2	2				21.688
21				0.005			0
22	17:47:39	Sun-09 April 2023	21.688	21.693	67,232.80		1	3,100						2	2				21.688
23				0.005			0
Test1

Cell Formulas
Range	Formula
J1	J1	=EXTRACTNUMBERS(M2,TRUE)
K1	K1	=SUM(J1+2)
L1	L1	=EXTRACTNUMBERS(K1)
L2	L2	=ADDRESS(L1,2)
M2	M2	=COUNTA(D:D)
F2	F2	=MIN($E$4:$E$17)
F3	F3	=MAX($E$4:$E$17)
E4,E22,E20,E18,E16,E14,E12,E10,E8,E6	E4	=IFERROR(IF(SUM(C4*H4)=0,"",(SUM(C4*H4))),"")
H4,H22,H20,H18,H16,H14,H12,H10,H8,H6	H4	=IF(YEAR(B4)=2022,$H$2,IF(YEAR(B4)=2023,$H$3,""))
N4,N22,N20,N18,N16,N14,N12,N10,N8,N6	N4	=SUM(IF($C$4:$C$22=C4,1,0))
O4,O22,O20,O18,O16,O14,O12,O10,O8,O6	O4	=COUNTIF($C$4:$C$22,C4)
P4	P4	=XLOOKUP(2,O4:O22,B4:B22,0)
R4,S4:S22	R4	=IF(B4=0,"",B4)
U4	U4	=INDEX($B$4:$B$26, SMALL(IF(COUNTIF($T$4, $O$4:$O$22)*COUNTIF($R$4, $B$4:$B$26), ROW($B$4:$O$22)-MIN(ROW($B$4:$O$22))+1), ROW(A1)), COLUMN(A1))
G5:G23	G5	=MOD(ROWS(G$2:G3),2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:D23	Expression	=$G4=0	text	NO

 

[MCLIFTO8]

If you're just looking to get the dates where there's duplicates, wouldn't a simple index match do?

Get nth match with INDEX / MATCH

To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. In the example shown, the formula in I7 is...

exceljet.net

 

[MrDB4Excel]

I tried, believe me, I tried to get this to work and it does not. But I have long ago given up on getting this to work because I decided it was not that important. But if someone comes up with a better working solution I am all ears.

 

[MCLIFTO8]

If you're still interested in troubeshooting then I have some questions. If you've given up that's OK too. Not all solutions are worth the time investment.

O8 showing the number 2, and with cell C8 showing 21.578, then the sought-after formula would provide a list of the two dates associated with the rate of 21.578.
Similarly, with cell O12 showing the number 3, and with cell C12 showing 23.763, then the sought-after formula would provide a list of the two dates associated with the rate of 23.763.

What does column O have to do with the results of the lookup?

My main trouble is I don't understand in plain terms what your inputs and outputs are.

for example,
Inputs: Column C has rates that are duplicates.
Output, on each row, columns P, Q, R, S, etc. Where the rates are duplicates, look up the date in column B and return the duplicate dates.

 

[MrDB4Excel]

MCLIFTO8, you hit the nail on the head. Everything past column C in my original post is totally useless and not needed. So the whole point of what I am after pertains to columns B & C.
All parts of my original post beyond column C I originally thought were relevant. Not so. Those were my feeble attempts at resolving the issue and so I thought were important.
Having had sufficient time since this original post I came to the conclusion pretty much as you have indicated and have tried in vain before your first reply yesterday along with your first reply at a resolution and keep hitting my head on that proverbial brick wall.
In short, I only need to get all dates relevant to any of those numbers in column C that are duplicates.

 

[MCLIFTO8]

MCLIFTO8, you hit the nail on the head. Everything past column C in my original post is totally useless and not needed. So the whole point of what I am after pertains to columns B & C.

In short, I only need to get all dates relevant to any of those numbers in column C that are duplicates.

Ok, this is simple. I've detailed the steps exactly because we're on a forum and can't talk over the phone.

Find nth match based on lookup

• In excel 365 you can use filter to get duplicates quite easily.
  You've listed Excel 2013 on your bio so I'm going with a different formula.
• In older versions of excel X lookup supports the first and last instance of a value. If your duplicates are limited to 2 or less a simple xlookup will suffice.
• Also in older versions of excel There are various "get nth match with vlookup" type formulas that use helper columns as well.
• Finally in older versions of excel There are "index match" lookups that do not require helper columns.
  We will go with this one.

Here's the solution I modified from ExcelJet and mentioned previously in the thread.
exceljet.net/formulas/get-nth-match-with-index-match

Original/ template formula
Important! Press Control + Shift + Enter when entering the formula.

=INDEX(return_array,SMALL(IF(lookup_array=lookup_value,(lookup_array)-ROW(INDEX(lookup_array,1,1))+1),nth_match))

Your formula

1. Put this in P4 with ctrl + shift + enter.
   

   =INDEX($B:$B,SMALL(IF($C:$C=$C4,ROW($C:$C)-ROW(INDEX($C:$C,1,1))+1),1))

   
   
   
   
   
2. select P4:P5, drag down your formula and it should insert your formula skipping a row as it goes down.
   
   
   
   
   
3. Then select all your formulas, drag them right 2-3 columns depending on how many duplicates you need to check.
   
   
   
   
   
   
4. Then highlight the second column of formuas, ctrl-h, find +1),1)) replace with +1),2)). for the third column replace +1),1)) with +1),3)), etc, etc.
   It has to be highlighted, otherwise you will destroy all the formulas not just the column you're working on.
   
   

You should end up with this.....

You can delete or modify columns A and D:O, they aren't used in the formula.

 

[MrDB4Excel]

Worked very well, thank you for going above and beyond.