If i have read this right there are a few options, the old skool formula works but you need to be careful with how many rows are input and output and duplicates make it more complex.
Do a Small on the sales column =SMALL(Sheet1!$D$2:$D$5,ROWS($A$2:A2))
then index/match to bring back the serial and index numbers
Probably the easiest solution is to use pivot tables but you need to put the sorted column first, but then updating could be as easy as a refresh
Then we have the new dynamic arrays which i think are only in Excel 365, but they are very cool - Filter With a sort =FILTER(SORT(Sheet1!A2:E5,4,1),{1,1,0,1,0})
See attached
|