IFERROR, INDEX, MATCH returning zeros instead of blanks

excel excel-formula excel-match

44,228

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

44,228

Author by

Laura Ligouri

Updated on April 06, 2020

Comments

Laura Ligouri about 4 years
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?
- Mark Ransom almost 9 years
  
  Unfortunately there is nothing you can do in Excel to get a blank cell, other than leaving it blank. You can get an empty string but it's not the same as a blank.
- Matt Cremeens almost 9 years
  
  @MarkRansom I'm guesssing an empty string is what the OP really wants. Use "" instead of 0.
- Matt Cremeens almost 9 years
  
  Oh, nevermind. Sorry.
- Matt Cremeens almost 9 years
  
  You could try going into File-->Options-->Advanced and make sure Show a zero in cells that have zero value is unchecked (untested).
- Laura Ligouri almost 9 years
  
  Hi Matt, thanks for your suggestions. I unfortunately don't have an "Options" within the File drop down menu. Any other suggestions?
- Matt Cremeens almost 9 years
  
  @LauraLigouri I don't at the moment. What is some sample input into the formula and what is the expected result?
- Admin almost 9 years
  
  @LauraLigouri - Try Alt+F, T for Excel 2010-13, the 'pizza-button' in the top left corner for Excel 2007 or for Excel 97-2003 use Tools, Options.
Laura Ligouri almost 9 years

Barry, you are a lifesaver! I tried something very similar but was one input off. This did the trick. Thanks SO MUCH!
FreeMan over 7 years

Year and a half later and this is exactly what I needed!