IFS Shows #VALUE! instead of actual value

[tombabcd]

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!

[#2]

@Hans Vogelaar 

I promise I will not keep abusing your hospitality.  I have tried to figure this out for days but am running out of time with so many priorities to get done.  I thought it would be much simpler than the solution you provided with the Index/Match.  I think this also requires that but it is somewhat different.

I am uploading a sample file of data.

Basically I am trying to look up a combination of the Coverage (col A) and Desc. (col B) of the statement tab against the last 3 positions of the $Segment (col A) and Year (i.e. Desc lookup) (col D).  I extracted the year from the Accd Date column since I didn't want to make the formula too long.

The other value to lookup is in Row 1 of the statement tab - values are Stat19A and Stat21A

against the Statistic column (col B) of the data tab.

I tried using an Xmatch but could not get it to work.

The other thing is I only need to bring back the last value of the row for each Year.  So 2010 is col N, 2011 is col M.  I wasn't sure if there was an easy way to just subtract 1 from the next row down or use the column headings of 1-10 which correlate to years in terms of 10 years of data for 2010, 9 years of data for 2011, 8 years of data for 2012, down to one year of data for 2019.

Again, I will not keep posting a question for every Excel question I have but if you can help with this I think between this and your first solution I will have enough to figure out the solutions to similar needs.

Does the "|" concatenate two arrays of data together for a search?

Thank you.

Tom

[#3]

@tombabcd 

I did get two columns of data to be looked up for Coverage and Desc. as the lookup values against the Segment and Year columns on the data tab.  However when I tried adding in the third lookup value of Stat19A, Stat21A, that will not work because it is not in the array format as the Statistic column is on the data tab.  Not sure if that is a hlookup or how to incorporate that lookup with the array lookup.  But I do see where the "*"&"|" combinations are required although I don't fully understand them.  I mean I get the & as an and and I assume the "|" is to concatenate array ranges together.  Leaving out the Stat19A and Stat21A lookup which are in Row 1 of the statement tab and column B (Statistic) of the data tab I have this which worked out fine.  Then I would need to pick up the last value of the corresponding row for all three criteria.  (I have the lookup data in another workbook.)

=MATCH("*"&D9&"*|*"&G9&"*",'[Loss Data EOY Paste Wksht.xlsx]Loss Data EOY Paste'!$B$2:$B$11&"|"&'[Loss Data EOY Paste Wksht.xlsx]Loss Data EOY Paste'!$N$2:$N$11,0)

[#4]

@tombabcd 

Excel has to perform a tremendous amount of lookups for this, so it becomes very slow.

[#5]

Thank you Hans. Without providing me the specifics is there something else you would recommend such as an IFS, X or H lookup, Xmatch, etc. Just curious what you would use.

And please let me ask you this again. What is the purpose of wrapping the Match reference data in asterisks such as "*" and "*|*. The answer to this will help me understand this much better. Thanks again!!!

[#6]

@tombabcd 

Normally, MATCH searches the lookup_array for the first cell whose value is equal to the lookup_value.

In the situation you're referring to, the cells in the lookup_array Reference!$D$15:$D$21 contain more text than just the lookup_value in M3. So the cell in Reference!$D$15:$D$21 should be the value of M3 with any text before or after it. This is indicated by the wildcards *: "*&M3&"*". & is the concatenation operator, it combines several text strings into one.

Since we want to do this for two lookup_ranges, I combined them with &, with "|" in between to separate them.

[#7]

Thank you. This helps a lot!!!