IFERROR, INDEX, MATCH returns zeros instead of spaces

Question

I use the following formula:

=IFERROR(INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))," ") 

This formula works beautifully, except for empty cells it returns "0". I want empty cells to return as empty.

In particular, this is what I have

Sheet 1 (entitled "Purified Mail")

 Name Email Age Gender Task #1 Andrew [e-mail] 18 1 80 Jason [e-mail] 20 1 95 Judy [e-mail] 18 2 __ Jack [e-mail] 24 1 65 

Sheet 2 (called Combined) - What I Get

  Email Task#1 [e-mail] 80 [e-mail] 95 [e-mail] 0 [e-mail] 65 

Sheet 2 (called Combined) - What I Want

  Email Task#1 [e-mail] 80 [e-mail] 95 [e-mail] __ [e-mail] 65 

What do I need to do to adjust this formula?

Answer 1

What values ​​does your formula return? If these are text values, just combine the “null string” with your INDEX/MATCH formula as follows:

 =IFERROR(INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))&"","") 

This also works for numbers, except that it converts them to text, so if you do not want you to try this version:

 =IFERROR(IF(INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))="","",INDEX('Cleaned Post'!W:W,MATCH(Combined!$C2,'Cleaned Post'!$C:$C,0))),"") 

Answer 2

I understand this is an old post, but ... I agreed to use conditional formatting ... if the return value was 0, change the color of the text so that it matches the background ...