9

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 888@gmail.com   18    1        80
Jason  687@gmail.com   20    1        95
Judy   432@gmail.com   18    2        __
Jack   236@gmail.com   24    1        65

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

    Email      Task#1
888@gmail.com   80
687@gmail.com   95
432@gmail.com    0
236@gmail.com   65

Sheet 2 (entitled Combined) - What I want

    Email     Task#1
888@gmail.com   80
687@gmail.com   95
432@gmail.com   __
236@gmail.com   65

What do I need to do to adjust this formula?

Ram
  • 3,092
  • 10
  • 40
  • 56
Laura Ligouri
  • 95
  • 1
  • 1
  • 5
  • 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. – Mark Ransom Jul 06 '15 at 20:49
  • 1
    @MarkRansom I'm guesssing an empty string is what the OP really wants. Use `""` instead of `0`. – Matt Cremeens Jul 06 '15 at 20:51
  • Oh, nevermind. Sorry. – Matt Cremeens Jul 06 '15 at 20:52
  • You could try going into File-->Options-->Advanced and make sure Show a zero in cells that have zero value is unchecked (untested). – Matt Cremeens Jul 06 '15 at 20:56
  • Hi Matt, thanks for your suggestions. I unfortunately don't have an "Options" within the File drop down menu. Any other suggestions? – Laura Ligouri Jul 06 '15 at 21:00
  • @LauraLigouri I don't at the moment. What is some sample input into the formula and what is the expected result? – Matt Cremeens Jul 06 '15 at 21:09
  • @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. –  Jul 06 '15 at 21:15

2 Answers2

21

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))),"")
Ram
  • 3,092
  • 10
  • 40
  • 56
barry houdini
  • 45,615
  • 8
  • 63
  • 81
0

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...

matt
  • 1