Hi,
Please could someone help with this problem...
I have my data tab with, amongst other information, a table containing customer details and the amount they have spent each day.
I have a summary page and using a LARGE formula I have extracted the top 15 amounts spent on a particular day (date is fixed in cell B1).
I now need to match the amount spent, and the day, to return the customer account number in column A of the summary page.
The problem is when I have two or three customers that have spent the same amount on the same day it only returns the first customer account number that matches the criteria.
When i have duplicate amounts spent I want to return each customer that has spent that amount.
In Summary:
Data tab -
Column D is customer account number
Column J is amount spent
Column X is the date
Summary tab -
B1 is the date
D5:D19 returns highest 15 values from particular date in descending order.
A5:A19 i want to match these values with the customer number.
So far I have tried x lookup with a helper column in column A of Data sheet with no success but now trying V Lookup.
My formula in A5 is =XLOOKUP(D5&$B$1,'Data'!K:K&'Data'!X:X,'Data'!D:D)
Customer Number Spent
DC1591 £12,015.00
SH1443 £9,000.00
JP1628 £3,496.88
BH1659 £2,800.01
TM1509 £1,680.01
SR1536 £1,117.33
DL1325 £1,000.00
CT1660 £350.00
CT1660 £350.00
CB1620 £277.41
VW1603 £200.00
PH1564 £185.00
AV1615 £100.00
AV1615 £100.00
OR1053 £91.00
In this example CT1660 is duplicated as is AV1615.
Many thanks,
Bookmarks