Change #N/A to Blank cell

openoffice-calc formulas
user3440968 picture user3440968 · Mar 20, 2014 · Viewed 40.1k times · Source

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.

Answer

ttaaoossuu picture ttaaoossuu · Mar 20, 2014

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

Related questions

How to quote a quote with the CONCATENATE function in OOCalc

In OOCalc I want to use the CONCATENATE function to add quotes to each string in column A. So in cell B1 I want to do: =CONCATENATE("\"",A1,"\"") OOCalc does not like this, or without the escaping backslash. Does anyone …

Read More
How to add number of days to a date, consider only business days (i.e. ignore weekends)?

I'm trying to create a formula to calculate the n-th Business Date (only Monday to Friday are business days). For simplicity's sake, holidays are not important; only weekends should be ignored. For example: +------------------------------------------------------------------ | A B C +------------------------------------------------------------------ 1 | Starting Date …

Read More
Join two spreadsheets on a common column in Excel or OpenOffice

I have two CSV files with a common column and I want to "Join" the tables together on the common column. For example: Join 'A' with 'B' equals 'Result'. If a one table has a key value that does not …

Read More