index match returns 0 for blank cell, want it to be "-"

Question

I have looked all over and tried a bunch of different things and non are working.

I can get the error to show - but I also want a blank cell to return -.

Right now blank cells are returning 0.

The blank cells appear in the $C$6:$DD$50 section if that helps.

=IFERROR(INDEX('Foundation Plates'!$C$6:$DD$50,MATCH($C9,'Foundation Plates'!$B$6:$B$50,0),MATCH(D$8,'Foundation Plates'!$C$5:$DD$5,0)),"-")

Jurisprudence

When you don't get zeroes what sort of values does the formula return, numbers or text? – Adhamh

Consider accepting the =Index(...) & “” answer. It's better because you don't need to repeat the formulas as in the currently accepted answer – Aftershaft

Answer 1

=Index(...) & “”

it would convert 0 (Blank value) to an empty string.

Thermit

this should be the recommended answer – Fidelis

Very ultra-slick! Thanks a lot. Could you please share how you knew of such a trick? – Vesperal

clever!.. it forces it to be a string (by adding the empty string) so it no longer guesses empty to be an integer (which turns it into a zero) and forces empty to actually = blank. So simple but effective. – Sawyer

This doesn't quite work, it converts all values to text which can be a problem if the destination needs to be formatted as a date. – Kerrin

I feel putting the empty string to the left of the & operator is preferable because it indicates right away that you are dealing with a string: ="" & INDEX(...) – Applaud

Answer 2

Your formula return a 0, that means a match is found but the value in the relevant cell is blank or 0. If the formula doesn't find any matching cell, the IFError will deal with this and return "-" in this case.

To hide Zero's from formula cells, you can use Custom Formatting to hide zeros.

Select the formula cells and custom format them using the format given below as per the existing formatting applied to the formula cells.

1) If formula cells have General Formatting, try this...

0;-0;;@

2) If formula cells have Currency Formatting, try this...

$#,##0.00_);($#,##0.00);

3) If the formula cells have Date Format, try this...

mm/dd/yyyy;;

If you want to show a "-" instead of blank in formula cells with zeros, change the custom formatting like below...

1) 0;-0;-;@

2) $#,##0.00_);($#,##0.00);-

3) mm/dd/yyyy;;-

Animism

This is the right answer, it's a formatting issue and not a value issue. 0/Blank are the same thing to Excel. – Feathercut

@pyskell Thanks for the comment. – Animism

Answer 3

Would adding an ISBLANK condition achieve your goal?

=IF(ISBLANK(<range>),"-",<your code>)

Disgrace

Answer 4

 =IFERROR(IF(INDEX('Foundation Plates'!$C$6:$DD$50,MATCH($C9,'Foundation Plates'!$B$6:$B$50,0),MATCH(D$8,'Foundation Plates'!$C$5:$DD$5,0))=0,"-",INDEX('Foundation Plates'!$C$6:$DD$50,MATCH($C9,'Foundation Plates'!$B$6:$B$50,0),MATCH(D$8,'Foundation Plates'!$C$5:$DD$5,0))),"-")

This Formula is like:
=IFERROR(If(Index =0,"-",Index),"-")
It will work because it test the Index if = 0 the result will be "-" if <>0 it will give the corresponding value

Marentic

it still functions the same as my code. blank cell returns 0. Error cell returns -. I must be missing something :( – Jurisprudence

you can use =IFERROR( If(Index =0,"-",Index),"-") but it will be too long that is why i and trying to find a shorter way – Marentic

Answer 5

I have this issue all the time. I've found two possible solutions, neither ideal.

Adding an &"" at the end is easy and works IF you don't need the returns as numbers, but I find it generally wreaks havoc having all the numbers return as text so avoid using that at all costs unless I'm certain it won't cause an issue. To avoid that, you can use:

=IF([yourINDEXformula]="","",[yourINDEXformula])

This works to give zeros as zeros but shows blanks as blank all while keeping numbers as numbers. Unfortunately, it is very cumbersome, especially with a long or complicated formulas, as you basically have to enter it twice.

Excel really needs an IFBLANK that works like an IFERROR...

Violation