Show next date in RANK if same value found

[redspanna]

Hi all

In cells P27:P31 I have the formula

=XLOOKUP(P21,$R$3:$R$33,$Q$3:$Q$33)

This will return the data for the RANK value held through cells P21:P25

NDA.v8.xlsm
	P	Q	R	S
3		01-Aug-20	0	18
4		02-Aug-20	0	18
5		03-Aug-20	165	7
6		04-Aug-20	70	13
7		05-Aug-20	70	13
8		06-Aug-20	70	13
9		07-Aug-20	470	6
10		08-Aug-20	140	8
11		09-Aug-20	0	18
12		10-Aug-20	0	18
13		11-Aug-20	0	18
14		12-Aug-20	475	4
15		13-Aug-20	475	4
16		14-Aug-20	2175	2
17		15-Aug-20	0	18
18		16-Aug-20	0	18
19		17-Aug-20	0	18
20	Rank	18-Aug-20	0	18
21	£26,275	19-Aug-20	70	13
22	£2,175	20-Aug-20	140	8
23	£1,125	21-Aug-20	70	13
24	£475	22-Aug-20	0	18
25	£475	23-Aug-20	0	18
26		24-Aug-20	140	8
27	Thu 27	25-Aug-20	140	8
28	Fri 14	26-Aug-20	1125	3
29	Wed 26	27-Aug-20	26275	1
30	Wed 12	28-Aug-20	140	8
31	Wed 12	29-Aug-20	0	18
32		30-Aug-20	0	18
33		31-Aug-20	0	18
Workings

Cell Formulas
Range	Formula
P21:P25	P21	=INDEX(SORT($R$3:$T$33,2),SEQUENCE(5)*1)
P27:P31	P27	=XLOOKUP(P21,$R$3:$R$33,$Q$3:$Q$33)
S3:S33	S3	=IF(ISNA(R3),"",COUNTIF($R$3:$R$33,">"&R3)+1)
Dynamic array formulas.

As you can see there is a day where the RANK day is more than one day (WED12)

Is there a way to change the formula through P27:P31 so that if a duplicate value is found, it will show the both?

So results through cells P27:P31 should read ..(without values)

Thu 27 (£26,275)
Fri 14 (£2,175)
Wed 26 (£1,125)
Wed 12 ( £475)
Thr 13 (£475)

Thanks in advance for any help

 

[Fluff]

How about the same way I showed you here avoid #N/A error using =RANK formula

 

[redspanna]

How about the same way I showed you here avoid #N/A error using =RANK formula

OK @Fluff I'll have a look and give that a go
Thanks