CODEWITHSUNDEEP
excelexcel-formula
MEF
MEF

Reputation: 49

Value fading after executing an IFERROR, INDEX/MATCH array formula in excel?

I came across an interesting issue today that I can't seem to find a solution on the internet for. I have a formula that looks like this:

=IFERROR(INDEX(Table,MATCH(1,(Table,Col1=A1)*(Col2= B1),0),3),"NOPE")

The formula works correctly, but in some cases where the return value is 0 (i.e. the value is blank in column 3 that is being returned, but there are appropriate matches in Col1 and Col2) something wonky happens. When I execute the formula the 0 value shows briefly and then slowly fades to blank. When you copy the cell and try to paste the value plain text it is also blank. What concerns me is this only happens in some cases, other cells have a 0 value returned and it remains in the cell.

My best guess is it has something to do with the cell formatting (even though it is set to general for all cells), but I'm at a loss. This doesn't really impact the performance of the spreadsheet, but it looks sloppy having some cells with 0's and others that are blank even though they indicate the same result.

Any help would be greatly appreciated.

Edit 2: Changed the values from numeric to text 1=A, 2=B, etc. and this did not solve the problem, there are still some stubborn cells with the issue described above.

EDIT 3: Haven't had any luck fixing the issue. My work around for the time being is IF(original formula)= 0,"", (original formula)) for uniformity so empty results are returned as a blank and not a 0 or blank. Strangely, I tried IF(original formula)= 0,0, (original formula)) and the zero still faded in some cells.

Upvotes: 0

Views: 176

Answers (1)

Skip
Skip

Reputation: 23

I have had this happen before. I suspect you might be using Office 365, and a recent update threw Excel on this one. What I got to work is to create a new worksheet, format the column in question from the beginning as a number (or currency), and then enter the formula into the cell (do not copy from the other worksheet). Paste this formula down and it should be a consistent "0" or "-".

I also have found that it helps to save the file as a ".xlsb" for larger files. This tends to add stability in all areas.

Upvotes: 0

Related Questions