redspanna
Well-known Member
- Joined
- Jul 27, 2005
- Messages
- 1,602
- Office Version
- 365
- Platform
- Windows
Hi all
In cells P27:P31 I have the formula
This will return the data for the RANK value held through cells P21:P25
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
In cells P27:P31 I have the formula
Excel 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