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)),"-")
Copy that cell (Ctrl+C), then select cells from C2 to C7 and use the 'Go to special' to select Blank cells only under Home tab, in Editing > Find & Select > Go To Special. Simply just Paste the formula to all the blank cells by Ctrl+V. You will get all the information in one row on the first row for each companies.
INDEX AND MATCH FORMULA, TO RETURN "0" WHEN THE VALUE IS "NO MATCH" — Smartsheet Community.
=Index(...) & “”
it would convert 0 (Blank value) to an empty string.
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>)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With