May 10 2021 10:42 AM
May 10 2021 10:42 AM
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!
May 10 2021 11:26 AM
You could use
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.
May 10 2021 11:58 AM
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.
May 10 2021 12:03 PM
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:
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:
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
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.
May 10 2021 02:28 PM
May 10 2021 02:59 PM
May 17 2021 12:29 PM
Thank you again for the help. It works great.
Can I ask about a portion of the formula.
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.
May 17 2021 12:40 PM
The last value in B2:K2 is returned by
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.
May 17 2021 12:43 PM
May 17 2021 12:48 PM