Ask Your Question

Why does this return #N/A?

asked 2021-04-18 12:48:59 +0200

abrogard gravatar image

updated 2021-04-18 23:40:23 +0200

I have this cell, H5, that returns #N/A when I think it should merely return a null. The cell it references is blank but does contain a formula. I've successfully been using IF(ref="".... to handle this up 'till now.

this is what's happening. what am I doing wrong here?

  in H5        =IF(L5="","",IF(B5="","",MATCH(L5,$A$1:A$30)+1))    #N/A
  in L5        =IF(B5="","",(INDIRECT("A"&ROW()))-1)              blank   
  in B5        03/02/2021

[Edit - Opaque] put code into preformatted textC:\fakepath\fortheweb.ods

edit retag flag offensive close merge delete


Shouldn't you use ; instead of , I don't know, if this us locale-dependend, but for me , means Excel and ; AOO and LO

Wanderer gravatar imageWanderer ( 2021-04-18 13:08:45 +0200 )edit

@Wanderer - using , (comma) sadly is the default for US locale (for the sake of maximizing sources of error and confusion ;-().


You did not tell us, what's in A5: "A"&ROW() is A5 hence INDIRECT("A"&ROW()) evals A5 and in due to lack of that information there is nothing, so (INDIRECT("A"&ROW()))-1 yields -1 (L5) for me (and not blank, which I don't understand at all). And trying to MATCH(-1;...) in column A results (again due to lack of that information) to #N/A.

PS: The whole thing is unclear to me. Why do you try to MATCH(L5,...) in $A$1:A$30 if L5 already contains, what you are trying to match. Hence you could use ROW()+1 as well (but may be I do miss something now)

Opaque gravatar imageOpaque ( 2021-04-18 13:19:27 +0200 )edit


Shouldn't you use ; instead of , I don't know, if this us locale-dependend

It is locale dependent. In locales that do not use , comma as decimal separator you can use that as function parameter separator (see Tools->Options->Calc->Formula) and is the default for those or at least some because that's what people are used to from English Excel, but ; semicolon (unlike Excel) works in all locales, which is why we use it here.

erAck gravatar imageerAck ( 2021-04-18 14:29:37 +0200 )edit

@abrogard: please attach a sample file with actual data in A1:A30 where the formulas are supposed to work on. Please do not use the Answer field, edit your original question to provide further details. Thanks.

erAck gravatar imageerAck ( 2021-04-18 14:34:00 +0200 )edit

thanks for the comment about ";" rather than "," - I didn't know which was better, they both seem to work. In A5 is a '1' What I'm meaning here is I'm specifying IF L5 = "" which it is. I'll go edit the question and provide a little spreadsheet. I did it. That should make things clearer. It's my own answer to my own question elsewhere.

abrogard gravatar imageabrogard ( 2021-04-18 23:38:42 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-04-19 00:02:18 +0200

Opaque gravatar image

updated 2021-04-19 00:05:03 +0200


you have deactivated Tools -> Options -> LibreOffice Calc -> View -> Section: Display -> Option: [ ] Zero values and hence you see a blank cell in L5, while it is in fact 0. There is no 0 in $A$1:A$30, hence there is no MATCH(0;$A$1:A$30) and you get #N/A.

PS: This sample proves the general rule, that it is bad idea to hide existing information from oneself.

Hope that answers your question

edit flag offensive delete link more


well it does, absolutely, thank you. wonder how I get around that? Ah... no problem, just use an 'or()'. Very handy. thanks. :)

abrogard gravatar imageabrogard ( 2021-04-19 01:00:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-18 12:48:59 +0200

Seen: 54 times

Last updated: Apr 19