The issue I am having is trying to use XLOOKUP on a table column that has been generated using a ranking formula.

The Rank column is created using the following formula so that it adjusts dynamically depending on the visible columns (auto-filtering) since RANK.AVG or EQ does not take that into account.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Table2[[#All],[Sales]],ROW(Table2[[#All],[Sales]])-MIN(ROW(Table2[[#All],[Sales]])),0,1)),--([@Sales]<Table2[[#All],[Sales]]))

The XLOOKUP I am using is simple

=XLOOKUP(1,Table2[Rank], Table2[Rep])

The idea is that the field at the top would show the top ranked performer based on the visible rows. The problem is that when filtering the data, the rank column adjusts as it should, but the XLOOKUP does not function as I would expect. It sticks with the originally ranked 1 or even does something complete weird that I can't explain when running combinations of filters in the region column..

Anyone have any advice how I could better structure this to make it function in the intended manner. Any help at all would be appreciated.

CodePudding user response：

You'll need to employ a similar construction to account for visible rows only, for example:

=LOOKUP(1,QUOTIENT(0,SUBTOTAL(3,OFFSET(INDEX(Table2[Sales],1),ROW(Table2[Sales])-MIN(ROW(Table2[Sales])),))*(Table2[Rank]=1)),Table2[Rep])

Not sure why you're referencing the table header row in your other formula.