Forum Discussion

tombabcd's avatar
tombabcd
Copper Contributor
May 10, 2021
Solved

IFS Shows #VALUE! instead of actual value

 

 

 

Cannot determine why #VALUE! is shown instead of 201.  Per help on IFS, the value should be returned for the first TRUE response which in this case is shown for Logical_test2 - TRUE and the value of 201 is what I want returned.  However the #VALUE! is shown in my spreadsheet instead.

 

Any help or suggestions will be greatly appreciated.  Thank you!

excel
Formulas and Functions

25 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 10, 2021

    tombabcd 

    IFS() check till first TRUE condition and skips FALSE condition. If no one TRUE condition is met formula returns #N/A error. However, if !VALUE# is result of checked condition, entire formula returns !VALUE# as well.

    In your case the workaround is to use ISNUMBER(FIND(...)) instead of FIND(). But better formula HansVogelaar  suggested.

    • tombabcd's avatar
      tombabcd
      Copper Contributor
      May 17, 2021
      Hi Sergei, thank you for your response earlier. Can I ask you where to seek advanced Excel training classes, courses, workshops, etc., where I can learn these advanced skills? I took Advanced and Intermediate Excel classed through Ed2Go but they didn't begin to cover these kinds of functions in depth. Thank you. Tom
    • tombabcd's avatar
      tombabcd
      Copper Contributor
      May 10, 2021
      Thank you very much Sergei! I will try the ISNUMBER per your suggestion. Thanks again!!!
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 10, 2021

    tombabcd 

    You could use

     

    =IFS(ISNUMBER(FIND(L151,Reference!E20)),Reference!A20,ISNUMBER(FIND(L151,Reference!E21)),Reference!A21,...)

     

    But this is probably much more efficient:

     

    =INDEX(Reference!$A$20:$A$50, MATCH("*"&L151&"*", Reference!$E$20:$E$50, 0))

     

    Adjust the ranges if needed.

    • tombabcd's avatar
      tombabcd
      Copper Contributor
      May 10, 2021

      HansVogelaar 

       

      Thank you @Hans!
      I should have included more data. The value I am searching on can be either a number or text in case this matters which I think in your first example probably would make a difference. Sorry for not being more comprehensive.

      For example cell E15 contains the following choices:
      A0, A1, A2, A3, A4, 89, OE
      If a value is found in this cell then return the value in cell A15 which is:
      101

      Then in a subsequent logical test the following choices are available in cell E20
      11, 13, M0 ,M1, M2 ,M3, M4
      If a value is found in this cell then return the value in cell A20 which is:
      106
      etc.
      Unfortunately I can have a lookup value in more than one reference range i.e. cell E21
      51, 55, 89, OE
      Values 89 and OE are also shown in cell E15. I was going to try and use and AND in my IFS lookup to include the values in column D which will make the combination of values in E and D unique. I am probably explaining this in a needlessly cumbersome way.
      This might make it easier to follow:

      Lookup table i.e. Reference tab

      So if a value is found in E and D then bring back value in A

      Lookup values:

      Thanks again for your help and suggestions.

      I have not used index and match but need to begin doing so.  I might need to convert the Reference values into an array.

       

Resources