CODEWITHSUNDEEP
excelexcel-formulaexcel-match
Laura Ligouri
Laura Ligouri

Reputation: 95

IFERROR, INDEX, MATCH returning zeros instead of blanks

I am using the following formula:

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

This formula is working beautifully, except that for blank cells, it's returning "0". I would like blank cells to be return as blank.

Specifically, this is what I have

Sheet 1 (entitled Cleaned Post)

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

Sheet 2 (entitled Combined) - What I'm getting

    Email      Task#1
[email protected]   80
[email protected]   95
[email protected]    0
[email protected]   65

Sheet 2 (entitled Combined) - What I want

    Email     Task#1
[email protected]   80
[email protected]   95
[email protected]   __
[email protected]   65

What do I need to do to adjust this formula?

Upvotes: 9

Views: 47946

Answers (2)

matt
matt

Reputation: 1

I realize this is an old post, but... I settled for using conditional formatting.. if the returned value was 0, change the text color to match the background...

Upvotes: 0

barry houdini
barry houdini

Reputation: 46451

What sort of values is your formula returning? If they are text values it's sufficient to concatenate a "null string" to your INDEX/MATCH formula like this:

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

That also works for numbers except it will convert them to text so if you don't want that you can 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))),"")

Upvotes: 22

Related Questions