Lookup a non-joined table

[danielact]

I have a Pivot Table where I have a ranking function as a dimension. It returns values from 1 to 10,000. I have a table that has labels for certain values within the range. I want to set the dimension so that instead of returning the rank, it will return the label.

So, if my table is like this:

Rank     Label

1          Best

5          Middle

10          Worst

for those three rankings, it should return the "Best", "Middle", or "Worst" label instead of 1, 5, or 10. The tables is not linked to anything, as the ranking is calculated in the Pivot Table, not in my data table, so I have nothing to link to.

[vgutkovsky]

I think the simplest way is through a pick(match(. So it would look like this:

pick(match(rank(sum(Sales)),1,5,10),'Best','Middle','Worst')

Regards,

Vlad

[danielact]

Thanks Vlad, that works but is a bit buggy. The reason it's buggy is because I sometimes have duplicates - if, on my 10,000 rows, I have two values that are the same, I can miss a value. On my testing, I didn't have a number 500 ranked - I had 499 twice, then 501, so it didn't return the value I need for 500.

[vgutkovsky]

Well if there's a tie for #499, why would you expect there to be a #500 or for one of those values to arbitrarily be ranked lower than the other?

[danielact]

In the rank function, for the first option, option 4 uses 499 for the first instance, then increases by one for each subsequent occurence. However, this is only behind the scenes - on the screen, you can either show the range or the lowest number. The match function seems to be only picking up the lowest number even with the option 4 selected.

I don't want to use 499 in my code because the numbers will change pretty often - and also if any filters are selected.

[vgutkovsky · Accepted solution]

Well rank is returned as a dual(text,num). You can try

pick(match(num(rank(sum(Sales))),1,5,10),'Best','Middle','Worst')

to maybe force it to use the numeric behind-the-scenes value.

[danielact]

Thanks a lot, that fixed it!