Remove zero values from Transpose function

I am using the transpose function to create a list for a data validation. But I can't seem to figure out how to remove the zero value from the array and so it appears in my validation. How can I make change the zeros to blanks.

This is the formula I am using:

=IFNA(TRANSPOSE(INDEX($B$2:$K$12,,MATCH('SheetA'!I3, $B$1:$K$1,0))),"")

If the formula is returning text rather than numbers/dates you can use

Formula:

Please Login or Register  to view this content.

Originally Posted by Fluff13

If the formula is returning text rather than numbers/dates you can use

Formula:

Please Login or Register  to view this content.

Question, what does $B$2:$K$12&"" do in the INDEX function?

It prevents a blank cell from becoming 0, but will also convert a numbers/dates into text.

Originally Posted by Fluff13

It prevents a blank cell from becoming 0, but will also convert a numbers/dates into text.

Thanks for the explanation.

You're welcome & thanks for the feedback.

Originally Posted by Fluff13

If the formula is returning text rather than numbers/dates you can use

Formula:

Please Login or Register  to view this content.

Thank you! This works perfectly!