How to read offset cells from dynamic sort array formula?
 

Hi,

I have two columns that I am working with. Column A has text labels,
and Column B has corresponding values. I would like to perform a
dynamic sort to show the value and corresponding text labels in order
from highest to lowest.

I have created an array formula to sort the values, and that works
fine:
{=LARGE(Data, ROW(INDIRECT("1:"&ROWS(Data))))}
(Note: "Data" is a named range)

The problems is that I can't seem to figure out how to pull the
corresponding text labels out of the adjacent column. It seems like I
should be able to use OFFSET to do it, but I can't seem to wrangle an
address from the sorting function to feed into OFFSET.

I'm grateful to anyone who can help.

Best wishes...

....Jay

How to read offset cells from dynamic sort array formula?
 

Hello Jay,

LARGE does not help you if some values are appearing twice or more
often.

Put into C1:
=COUNTIF($B$1:$B$999,""&B1)+COUNTIF($B$1:B1,B1)
Into D1:
=INDEX(A:A,MATCH(ROW(),$C$1:$C$999,))
And into E1:
=INDEX(B:B,MATCH(ROW(),$C$1:$C$999,))
and copy down as far as necessary.

Hope that helps,
Bernd