Excel 2007 : Multiple Criteria (nonUnique) Lookups

Works great except when my value is less than the least value in the table and then I get a "#N/A" returned. I am going to need to think about this some more.

For example: Lookup values: 29, 100, 18.1 results in #NA from the following subsection of the data:

29 100 213.566 6.5 0 45.8
29 100 303.337 13 0 59.1
29 100 394.273 19.5 0 71.7
29 100 493.166 26 0 84.8
29 100 599.165 32.5 0 98.1
29 100 706.662 39 0 111.0
29 100 814.558 45.5 0 123.3
29 100 929.922 52 0 135.9
29 100 1034.333 58.5 0 146.8
29 100 1140.452 65 0 157.3

Where I would want it to return the 45.8 value but because the 18.1 is less than the 213.566 it is throwing it off.

That seems to do the trick. It got rid of the #N/A errors. More testing but looks good so far. Thanks very, very much. Still trying to understand the nuances of the formula but I am indebted to you for the solution. Thank you again.

Any problems using named ranges in the code?

I am getting unexpected results when I use the code to find a different cell (so I have three things I am looking up: Temperature, Humidity (non-unique) and the Cooling Capacity OR Temperature, Humidity and the Heating Capacity and I want to return Cooling Capacity, Power Output, Fuel Input OR Heating Capacity, Power Output, Fuel Input. I am getting unexpected results with Heating Capacity as the lookup value and the Power Output and Fuel Input results are not what I would expect. For example:

Values:

Cooling Heating Temperature Humidity Power Output (Heating Power Output (Cooling) Cooling Heating Fuel Input (Heating) Fuel Input (Cooling)

103.6 117.3 86 60.0 311.8 128.6 93.7 311.8 311.840 147.660


And from the lookup table data:

Temperature |Humdity| Heating Cap | Power Demand |Cooling Cap | Power Out| Fuel Input

86 60 311.840 6.5 41.0 25.884 60
86 60 427.077 13 53.8 51.694 80.73
86 60 548.576 19.5 66.5 77.423 101.78
86 60 684.087 26 79.9 103.053 124.13
86 60 832.472 32.5 93.7 128.564 147.66
86 60 983.754 39 106.7 153.975 171.29
86 60 1132.300 45.5 118.5 179.277 194.86
86 60 1276.933 52 129.1 204.465 218.27
86 60 1317.056 58.5 131.9 211.064 224.62
86 60 1317.056 65 131.9 211.064 224.62


I would expect the following: Looking up on Cooling Cap, Temperature and Humidity with my input of 103.6 to yield: Power Demand: 128.564 Fuel Input 147.66 Cooling Cap 93.7

(which it does) and for Heating Cap, Temperature, and Humdity with my input of 117.3 to yield: Power Demand: 41.0 Fuel Input: 60 and Heating Cap: 311.840 which only the Heating Cap works out correctly. The other two have values of 311.840 . The only differences between the two lookups is the swtiching of Heating Cap and Cooling Cap (range names) in each, other than that (and a small math expression for the heating as it is in MMBtu and I need to get it to MBtu for the lookup so I am multiplying by 1000 as an expression in the heating lookups:

=LOOKUP(9.99999E+307,CHOOSE({1,2},INDEX(CoolCapRange,MATCH(1,INDEX((AmbientTempRange=AA4940)*(RelHumidRange=AB4940),0),0)),LOOKUP(2,1/((AmbientTempRange=AA4940)*(RelHumidRange=AB4940)*(CoolCapRange<=(W4940))),PowerOutRange)))

=LOOKUP(9.99999E+307,CHOOSE({1,2},INDEX(HeatCapRange,MATCH(1,INDEX((AmbientTempRange=AA4940)*(RelHumidRange=AB4940),0),0)),LOOKUP(2,1/((AmbientTempRange=AA4940)*(RelHumidRange=AB4940)*(HeatCapRange<=(X4940*1000))),PowerOutRange)))


I have also made all of these formulas array formulas by CTRL-SHFT-ENTER.

Scratching my head.

Should I duplicate the tables and use one for Cooling and the other for Heating? Not sure how that would matter as as the heating capacity increases so does the cooling. But I haven't specifically sorted on the cooling....

Looks like it is when I am at the bottom of the range. I will try and create a link to the file somewhere.... http://www.zipboing.net/getfiles/ModelSubset.xlsx

http://www.zipboing.net/getfiles/ModelSubset.xlsx

Perhaps that link didn't work (for some reason it showed up as a zipfile so I purposely made it a zip file):

http://www.zipboing.net/getfiles/Model.zip

Unfortunately it is too large. I can try and hack it down further but it might not show the behavior that is errant than.

The last zip file works ok for me.

Check down around row 4940, it seems to not be correct and it looks like the low Heating Value throws it off some how. The values (for the heating cap, power output - heating and fuel input heating )are all the same number and they shouldn't be.

Here is a snapshot of the behavior:

Yee Haw. That was it. Seems to all be looking up values as expected....

Thank you very much.