CODEWITHSUNDEEP
excelexcel-formularange
Girl007
Girl007

Reputation: 175

Ignore blank cells in my Excel INDEX formula

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?

Upvotes: 1

Views: 2897

Answers (2)

EvR
EvR

Reputation: 3498

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)))),"")

Upvotes: 1

P.b
P.b

Reputation: 11438

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<>""))))

Upvotes: 0

Related Questions