SWITCH function shortcuts to #N/A

[cagdasalagoz]

Hello everyone,

I have 3 sheets that holds values for my stocks, funds and forexs.

I’m trying to create a macro that will use the type at column A and refer to the corresponding sheet to look
for the requested value. This is the macro I’ve created:

=SWITCH( A2, "FUND",VLOOKUP(B2,$Fund.A$3:C$35,3,0), "STOCK",VLOOKUP(B2,$Stock.A$3:C$35,3,0), "FX",VLOOKUP(B2,$Fx.A$3:C$35,3,0))

This macro above is returning the correct values only for FUND rows. All other rows gets #N/A

This one only works for STOCK rows. All others gets #N/A

=SWITCH( A3,"STOCK",VLOOKUP(B3,$Stock.A$3:C$35,3,0), "FUND",VLOOKUP(B3,$Fund.A$3:C$35,3,0), "FX",VLOOKUP(B3,$Fx.A$3:C$35,3,0))

This one only works for FX rows. All others gets #N/A

=SWITCH( A3,"FX",VLOOKUP(B3,$Fx.A$3:C$35,3,0),"STOCK",VLOOKUP(B3,$Stock.A$3:C$35,3,0), "FUND",VLOOKUP(B3,$Fund.A$3:C$35,3,0))

But when I write them sperately they work fine.

=VLOOKUP(B5,$Fx.A$3:C$35,3,0)
=VLOOKUP(B8,$Stock.A$3:C$35,3,0)
=VLOOKUP(B18,$Fund.A$3:C$35,3,0)

As you can see from the image below, the last VLOOKUP retrieves the correct value but it won’t resolve as the SWITCH statement’s result value.

Also I’ve noticed when I replaced the VLOOKUP with some string value, It works fine. So this means it evaluates the statement and yet it doesn’t return the value to the SWITCH when using VLOOKUP.

For example this one prints THIS IS A STOCK when A9 is STOCK

SWITCH( A9,"FX",VLOOKUP(B9,$Fx.A$3:C$35,3,0),"STOCK","THIS IS A STOCK", "FUND",VLOOKUP(B9,$Fund.A$3:C$35,3,0))

Am i doing something wrong in here?

[joshua4]

Wrap all of your VLOOKUP()'s in IFNA(). Example:

IFNA(VLOOKUP(B9,$Fx.A$3:C$35,3,0),"")

SWITCH() appears to ‘shortcut’ on seeing any #N/A. This does not seem to be well documented in the main user documentation.

Can someone test MS Excel behavior? (I only go up to 2016, no SWITCH()).

This feels buggy…certainly not what I would call expected behavior, but then expectation is such a personal thing.

Oh, here’s a sample spreadsheet.

SwitchAndNA.ods (23.2 KB)

[cagdasalagoz]

Thanks a lot joshua, this solves the problem. About the expected behavior I am a full time software developer with over 5 years of experience yet I could not figure out the reason of this issue. Documentation doesn’t state that and a regular user probably have a hard time using these functions in combined. This is definitely a bug if you ask me. It should not fail because of a VLOOKUP which it doesn’t required to run.

Thank you. Have a nice day.

[mikekaganski]

@cagdasalagoz

This is definitely a bug if you ask me.

You might well be right. You might even argue that it works fine when you use NA() directly as one of the resultN arguments. But what to do with bugs is described here:

This is the guide - How to use the Ask site?

Ask a question To do this, click on “+ New topic” in the top bar. [EN Button New Topic] The “Create a new topic” dialog appears. [EN Dialog Create a new Topic] In the “Type title, or paste a link here” line, enter as short a title as possible for the question. In any case, avoid entering the entire content of your question in the “Title” line. If you create your question in the “English” category, “English” will appear automatically. To the right of “Select at least 1 item”, you can sel…

[erAck]

That’s tdf#111675.

[joshua4]

Are these the same bug? Please take a look at this ODS with the demos side-by-side and see if you still think so.

SwitchAndNA2.ods (15.6 KB)

[erAck]

Demo1 isn’t directly related. Apart from that using CURRENT() in IFS() condition arguments doesn’t do what one would expect (except in the last condition), besides that CURRENT() easily fools expectations anyway if one doesn’t exactly know what it does. But here the IFS() and IF() result is already the propagated #N/A error because all conditions compare with B3 that is =NA().

However, for B5 it is not what is described as “In Demo 1 IFS falls through past the correct 4 choice clear to the (apparently) last choice”. It does not fall through, the applied Red style persists, no style is applied by the formula (try changing all 3 style names to "Gelb" and you’ll see, or apply the Grün style first and then recalculate). STYLE() is not executed as the #N/A error is propagated.

Also, in B4 the alleged “Nested IF: correct” observation is not correct, also there STYLE() is not executed as the error is propagated. Try changing all 3 styles to "Red" or apply the Grün style first and then recalculate.

For Demo2 I didn’t investigate deeper, but it looks related, not isolated into jump paths passing the encountered #N/A error further around.

[joshua4]

Thank you for spending some time on this. Since you had cited the bug above I thought you had recently looked at the demo ODS for that bug. That ODS was the source for Demo 1 on my ODS. Since you did cite that bug, I thought you were saying that it was related to the OQ for this thread.
As for

@erAck

For Demo2 I didn’t investigate deeper, but it looks related, not isolated into jump paths passing the encountered #N/A error further around.

I honestly just don’t understand what you are saying, or else my explanation in my ODS was so unclear that you didn’t pick up on what I meant.

What I meant might best be related by oversimplifying. The oversimplification would say this: While you might expect SWITCH to just die and return #N/A at any moment it encounters an #N/A from any of its return-value subfunctions, and you might expect it to evaluate every branch so that any #N/A anywhere would force an overall #N/A as the return, that is not what happens. It also doesn’t seem to reach an #N/A subfunction return then die without evaluating later branches. Rather, it seems to harbor a grudge against VLOOKUP so that if a VLOOKUP returns #N/A in any branch leading up to the correct (selected) branch, then it returns #N/A even if the correct (selected) VLOOKUP itself does not return #N/A. BUT if a later branch is just a constant return-value, SWITCH is happy to return it as if there were no possible #N/A’s anywhere at all.

Could you clarify your point, or perhaps in fact investigate a bit more by playing with Demo 2? Again, I thought you would recognize that Demo 2 was the added material…sorry, that was my bad assuption.

[erAck]

It depends on what arguments need to be evaluated or whether they are constant values that can be directly pushed to the evaluation stack or not. It’s independent of VLOOKUP(), any other evaluation would do similar and carry the error over to the next argument’s result. As a simple example,

=SWITCH(1;0;NA();1;23)

returns 23 because the constant argument 23 does not need evaluation, but already

=SWITCH(1;0;NA();1;TRUE())

returns #N/A error because the error of NA() is carried over to the results of all subsequently evaluated arguments of the same function, here the result of TRUE(). While internally IFS() and SWITCH() are classified as error evaluating functions to not have evaluation bail out already even before the functions are called, that mechanism can’t cope with the situation of multiple arguments representing different code paths. The current implementation simply does not suit these use cases.

[erAck]

Fwiw, that error inheritance is fixed, see https://bugs.documentfoundation.org/show_bug.cgi?id=111675#c24 and pending review for 7-3.