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 A723 = Brad

Cell B723 = 1234 Svc Code

Name Range = Kelly    

Svc Code = 1233              column 12 = .3831

 

Example #1:  This one works and gives me back the result of .3831

=IFS(A722="Barb",VLOOKUP(B722,Kelly,11,FALSE),A722="Betty",VLOOKUP(B722,Kelly,11,FALSE),A722="Bridget",VLOOKUP(B722,Kelly,13,FALSE),A722="Brad",VLOOKUP(B722,Kelly,12,FALSE),TRUE,"No Match")

 

  • It matches on Brad
  • It looksup value in B722 = 1233 and looksup value in Kelly - Named worksheet = Kelly
  • It found a match AND returned a value of.3831

 

Example #2:  Does not find the match in Kelly = 1234 and it returns #N/A instead of "No Match"

'=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 matches on Brad
  • It looksup value in B723 = 1234 and looksup value in Kelly - Named worksheet = Kelly
  • It did NOT find a match AND returned a value of #N/A (this is actually showing as an error)
  • I was expecting a "No Match" value

 

Any suggestions would be awesome -- I am about to pull my hair out 🙂

Excel 365

 

Thanks

B

 

 

 

 

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.

         

         

Resources