Change #N/A to Blank cell

问题:

How can I change the #N/A to the blank cell if nothing in that cell?

Eg. =VLOOKUP(B19:B36;$TABLE.A1:C46;2;0)

I think I might need something like an ISERROR check but I don't 100% know what I'm doing.

回答1:

If we're talking about Excel 2010 and later you can use IFERROR:

=IFERROR(VLOOKUP(B19:B36;$TABLE.A1:C46;2;0);"")

You can also put text into the final string result

回答2:

The question was misleading about the spreadsheet software as 2 different spreadsheets were originally stated in tags. However, it can be seen that the question is about OpenOffice Calc:
- Absolute sheet referencing ($ sign before the sheet name) is not possible in Excel.
- We also see a dot between the sheet name and the range, which is again not possible in Excel.

As in OpenOffice Calc you don't have IFERROR function, the only way is to repeat your main function twice in the following form (you can use both, ISNA and ISERROR, but I suggest ISNA as it's more specific and fits your case):

=IF(ISNA(YourFormula);"";YourFormula)

In your case something like:

=IF(ISNA(VLOOKUP(B19;$TABLE.A1:C46;2;0));"";VLOOKUP(B19;$TABLE.A1:C46;2;0))

You may want to make absolute reference to the range where you look for matching values, as I see you want to copy the formula down.

=IF(ISNA(VLOOKUP(B19;$TABLE.$A$1:$C$46;2;0));"";VLOOKUP(B19;$TABLE.$A$1:$C$46;2;0))

回答3:

Since the cells will contain a formula this is about appearances, so Conditional formatting might suit, say if the cell background is white, for style choose Font > Font Effects > Font color white.

For this, select the relevant range - I have assumed D19:D36 - and Format > Conditional Formatting... and for Condition 1 choose Cell value is and equal to and:

ISNA(D19)