Why does this return #N/A?

[abrogard]

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 textfortheweb.ods

[Wanderer]

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

[anon73440385]

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

@abrogard

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)

[erAck]

@Wanderer

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]

@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.

[abrogard]

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.

[anon73440385]

Hello,

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

[abrogard]

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