SOLVED

IFS Shows #VALUE! instead of actual value

Copper Contributor

 

tombabcd_1-1620668306813.png

 

tombabcd_0-1620668286763.png

 

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!

25 Replies

@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 

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 @Hans Vogelaar  suggested.

@Hans Vogelaar 

 

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

tombabcd_1-1620672980000.png

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

Lookup values:

tombabcd_2-1620673273654.png

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.

 

@tombabcd 

Could you attach a small sample workbook (without sensitive information)?

@tombabcd 

That could be like

=XLOOKUP("*"&G1&"*"&H1&"*",D:D&E:E,A:A,"nothing",2)

@Hans Vogelaar 

 

Hi Hans,

 

Yes, gladly will upload a test file which is attached.

 

Thank you!

 

Tom

Thank you very much Sergei! I will try the ISNUMBER per your suggestion. Thanks again!!!
Hi Sergei, I have never used XLOOKUP but learning all kinds of new, useful functions. Thank you for taking the time to respond!!!
best response confirmed by tombabcd (Copper Contributor)
Solution

@tombabcd 

See the attached version.

Thank you Hans! I feel a bit guilty for not taking the tips and figuring this out myself which I had every intention of doing but barely finished getting ledger entries in by the deadline. I cannot thank you and @Sergei enough. Thank you both for taking the time for responding and thank you again for taking the time and effort to get this working. I can't wait to explore each function and learn more about how they work and the best situations to use them in.

I have taken several Excel training classes but they don't begin to touch on things like this.

Thanks again!!!

@tombabcd , glad it helped, good luck with Excel

@Hans Vogelaar 

 

Hi Hans,

 

Thank you again for the help. It works great.


Can I ask about a portion of the formula.
=INDEX(Reference!$A$15:$A$21,MATCH("*"&M3&"*|*"&N3&"*",Reference!$D$15:$D$21&"|"&Reference!$E$15:$E$21,0))


Is the "*"& making this a wild card search or converting the number to text or both?
Is the *|* concatenating two columns of search data together?

 

I have searched in vain for the meaning of the "|" in Excel but have not found anything.


Also I have read the Excel help on Arrays, Index and Match but have not found the answers.

 

While I am at it can I ask the following:
If I want to pick up the last amount in a row is there an easy way to do so, i.e. pick up 1,000 for 2010,

550 for 2011, 580 for 2012, 490 for 2013, etc.

Year                1                2                3                4                5                6                7                8                9              10
2010           100           200           300           400           500           600           700           800           900        1,000
2011           150           200           250           300           350           400           450           500           550 
2012           230           280           330           380           430           480           530           580  
2013           310           340           370           400           430           460           490   
2014           350           360           370           380           390           400    
2015           370           380           390           400           410     

@tombabcd 

The last value in B2:K2 is returned by

=LOOKUP(9.99999999999999E+307,B2:K2)

9.99999999999999E+307 is the very largest number that can be entered in an Excel worksheet; it won't be found so LOOKUP returns the last value it encounters in the specified range.

The formula can be filled/copied down.

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
Thank you Hans. I realize that a significant part of solutions such as these is being able to think creatively in order to come up with solutions such as this. However, is there a book or course you recommend to help with learning more in depth about Excel? I have taken Advanced and Intermediate Excel classes through Ed2Go but they did not go into depth for these kinds of functions. Again, I realize that much of this is up to the user to know how to combine functions and use a particular function to bring back a value but it would be helpful to have a book or take a course that can get me further along than I am now. Thanks again.

@tombabcd 

Some sites I have found very useful:

Exceljet 

Contextures 

Thank you.
1 best response

Accepted Solutions
best response confirmed by tombabcd (Copper Contributor)
Solution

@tombabcd 

See the attached version.

View solution in original post