SOLVED

# IFS Shows #VALUE! instead of actual value

Occasional Contributor

# 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!

25 Replies

# Re: IFS Shows #VALUE! instead of actual value

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))

# Re: IFS Shows #VALUE! instead of actual value

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.

# Re: IFS Shows #VALUE! instead of actual value

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.

# Re: IFS Shows #VALUE! instead of actual value

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

# Re: IFS Shows #VALUE! instead of actual value

That could be like

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

Hi Hans,

Thank you!

Tom

# Re: IFS Shows #VALUE! instead of actual value

Thank you very much Sergei! I will try the ISNUMBER per your suggestion. Thanks again!!!

# Re: IFS Shows #VALUE! instead of actual value

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 (Occasional Contributor)
Solution

# Re: IFS Shows #VALUE! instead of actual value

See the attached version.

# Re: IFS Shows #VALUE! instead of actual value

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!!!

# Re: IFS Shows #VALUE! instead of actual value

@tombabcd , glad it helped, good luck with Excel

# Re: IFS Shows #VALUE! instead of actual value

Hi Hans,

Thank you again for the help. It works great.

=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.

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

# Re: IFS Shows #VALUE! instead of actual value

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.

# Re: IFS Shows #VALUE! instead of actual value

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

# Re: IFS Shows #VALUE! instead of actual value

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.

# Re: IFS Shows #VALUE! instead of actual value

Some sites I have found very useful:

Hi

Thank you.