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

 =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


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


Would adding an ISBLANK condition achieve your goal?

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

=Index(...) & “”

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

Tags:

Excel

Formula