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
=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.
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:
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.
May 10 2021 12:14 PM
Could you attach a small sample workbook (without sensitive information)?
May 10 2021 12:31 PM
May 10 2021 02:25 PM
May 10 2021 02:27 PM
May 10 2021 02:28 PM
May 10 2021 02:34 PM
SolutionSee the attached version.
May 10 2021 02:59 PM
May 11 2021 02:10 AM
@tombabcd , glad it helped, good luck with Excel
May 17 2021 12:29 PM
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 |
May 17 2021 12:32 PM
May 17 2021 12:40 PM
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.
May 17 2021 12:43 PM
May 17 2021 12:48 PM
May 17 2021 01:50 PM
May 10 2021 02:34 PM
Solution