Forum Discussion

balopez66's avatar
balopez66
Copper Contributor
Mar 01, 2023

Formula Help in Excel

I need help --- I am down to the last issue.  Please help with any suggestions to resolve example #2 below.  Thanks!!!!!   General Information:   Cell A722 = Brad Cell B722 = 1233 Svc Code Cell ...
excel
FikturFox's avatar
FikturFox
Brass Contributor
Mar 01, 2023
In your second example, A723 = "Brad" returned True, therefore it is going to execute your VlookUp() function. Unfortunately, it did not find anything so the VlookUp() returned #N/A. So, your IFS() function will terminate there and it is not going to return "No Match". What you can do is to wrap your VlookUp() with IfNa() function. ie IFNA(VlookUp(),"No Match").
balopez66's avatar
balopez66
Copper Contributor
Mar 01, 2023
You suggest putting IFNA at the beginning?
=IFS(A723="Barb",VLOOKUP(B723,Kelly,11,FALSE),A723="Betty",VLOOKUP(B723,Kelly,11,FALSE),A723="Bridget",VLOOKUP(B723,Kelly,13,FALSE),A723="Brad",VLOOKUP(B723,Kelly,12,FALSE),TRUE,"No Match")
it doesn't seem to be working or i am not doing something correct. Does IFNA's work with all my IFS?


  • FikturFox's avatar
    FikturFox
    Brass Contributor
    Mar 01, 2023

    balopez66 

    Hi. I am not suggesting to wrap your IFS() function with IFNA().
    With IFS(), it evaluates every condition that you put. In your example, you have 5 conditions. The 1st one returns False, so it moves to evaluate the 2nd condition, which is again False, then moves to evaluate the next condition (3rd) which is also False. Then comes the 4th condition which evaluates to True, so it then executes VLOOKUP(B723,Kelly,12,FALSE) but returned #N/A. The 5th condition (TRUE, "No Match") is not going to be evaluated because the previous condition (4th) was evaluated to be True. It stops there and moves to execute that vlookup() function which returned #n/a. Should that 4th condition returned False, then you will have an answer of "No Match". Now if you do not want #n/a as a result from the vlookup(), then wrap it with ifna().

    ie IFNA(VLOOKUP(B723,Kelly,12,FALSE) , ""), this will now return blank or empty.

     

     

    • balopez66's avatar
      balopez66
      Copper Contributor
      Mar 01, 2023
      Thank you so much for your knowledge:
      Modified formula:
      =IFS(A723="Barb",VLOOKUP(B723,Kelly,11,FALSE),A723="Betty",VLOOKUP(B723,Kelly,11,FALSE),A723="Bridget",VLOOKUP(B723,Kelly,13,FALSE),A723="Brad",VLOOKUP(B723,Kelly,12,FALSE),IFNA(VLOOKUP(B723,Kelly,12,False),"")

      This gives me "The Formula is missing an opening or closing parenthesis".
      • FikturFox's avatar
        FikturFox
        Brass Contributor
        Mar 01, 2023

        balopez66 

        Your formula should be modified like this:

        =IFS(
A723="Barb", IFNA(VLOOKUP(B723,Kelly,11,FALSE),""),
A723="Betty", IFNA(VLOOKUP(B723,Kelly,11,FALSE),""),
A723="Bridget", IFNA(VLOOKUP(B723,Kelly,13,FALSE),""),
A723="Brad", IFNA(VLOOKUP(B723,Kelly,12,FALSE),""),
TRUE,"No Match")

         

Resources