Ignore blank cells in my Excel INDEX formula

1
votes

I have the following table on Excel from A2-A10

YEAR
1999
1997

1999
1998
1998

1996
1999

=INDEX(SORT(UNIQUE($A$2:$A$10)),COLUMN((A1))) >>> (drag formula horizontally)

This is my output:

1996  1997 1998 1999 0

The problem is that it keeps returning 0 because my range contains some blank cells. How can I ignore blanks using this formula?

2
excelexcel-formularange
Try adding ifblank() or if > 0 ... – Solar Mike

2 Answers

1
votes

three possibilities:

=INDEX(SORT(UNIQUE(IF($A$2:$A$10<>"",$A$2:$A$10,LARGE($A$2:$A$10;1)))),COLUMN((A1)))

=INDEX(SORT(UNIQUE(FILTER($A$2:$A$10,$A$2:$A$10<>""))),COLUMN((A1)))

=IFERROR(1/(1/INDEX(SORT(UNIQUE($A$2:$A$10)),COLUMN((A1)))),"")
0
votes

Or skipping INDEX so you don't need to drag your formula to the right: =TRANSPOSE(SORT(UNIQUE(FILTER($A$2:$A$10,$A$2:$A$10<>""))))