6

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.

ZygD
  • 22,092
  • 39
  • 79
  • 102
user3440968
  • 61
  • 1
  • 1
  • 2

3 Answers3

8

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

miken32
  • 42,008
  • 16
  • 111
  • 154
ttaaoossuuuu
  • 7,786
  • 3
  • 28
  • 58
  • The question is misleading about the spreadsheet software as 2 different spreadsheets were stated in tags. However, it can be seen that the question is not about Excel. 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. Finally, in OpenOffice Calc there is [no IFERROR function](https://bz.apache.org/ooo/show_bug.cgi?id=92677). So the answer does not answer the question. – ZygD Nov 18 '15 at 19:30
  • The formula above has a typo, I edited the answer but @miken32 rolled it back. Excel help page lists proper syntax. https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611 – Chris Nov 19 '15 at 17:32
  • @chris - it does not have a typo in the place that you mentioned. In fact, this is not a valid Excel syntax at all, but *not* because of what you offered to change. This is OpenOffice Calc! The answer itself **should be deleted** as there are no IFERROR in OpenOffice Calc. Your edit also did not make sense, because using commas or semicolons depends on Locale! Some areas use commas, some use semicolons. And you cannot have both of them in a formula - it is either commas or semicolons. – ZygD Nov 20 '15 at 22:43
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))
ZygD
  • 22,092
  • 39
  • 79
  • 102
0

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)
pnuts
  • 58,317
  • 11
  • 87
  • 139