Excel 2007 : Multiple Criteria (nonUnique) Lookups

[nutmeg34]

Pulling my hair out. My data is such that I want to look up by Temperature (not unique) and Humidity (not unique) and by Heating Load (not exact, nor unique) to return Power Demand and Fuel: A subset of the data would be such as:

Tamb (F) Rel_Hum (%) Heating Capacity (MBH) PowerDemand (kW) FuelPressure (0/1) Cooling Capacity (Ton) Power Output (kW)
59 0 240.220 6.5 0 48.7 25.894
59 0 341.768 13 0 63.2 51.722
59 0 449.750 19.5 0 77.8 77.477
59 0 569.015 26 0 93.1 103.143
59 0 694.804 32.5 0 108.4 128.716
59 0 821.576 39 0 123.0 154.206
59 0 947.941 45.5 0 136.8 179.600
59 0 1073.918 52 0 149.9 204.889
59 0 1200.767 58.5 0 162.3 229.049
59 0 1200.767 65 0 162.3 229.049
59 20 240.109 6.5 0 48.7 25.894
59 20 340.916 13 0 63.0 51.722
59 20 449.493 19.5 0 77.8 77.477
59 20 568.679 26 0 93.0 103.143
59 20 694.327 32.5 0 108.3 128.716

for example I want to look up based on 59 Tamb and 20 RelHumidity and a heating value of 463.4 and return a power demand of 77.477. The heating values should be less than or equal to the value in the data. That's the part that is throwing me. Since the values for heating won't match (in most cases) a value in the table I want to pick the value that is just lower in value and then return the power demand associated with those three inputs: tamb, relhumidity and heating value. I can sort the table and move columns around. I need to do this 8,760 times X 3 different values (power demand, cooling and fuel input).

[NBVC]

As long as you have the tables sorted as per your sample, i.e first by column A, then by Column B, then by column C, then you can use a formula like:

=LOOKUP(2,1/(($A$2:$A$16=K2)*($B$2:$B$16=K3)*($C$2:$C$16<=K4)),$G$2:$G$16)

where A2:G16 contains the table, and K2:K4 contain the respect inputs for column A:C lookups...

[nutmeg34]

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.

[NBVC]

Perhaps?

=LOOKUP(9.99999E+307,CHOOSE({1,2},INDEX($G$2:$G$16,MATCH(1,INDEX(($A$2:$A$16=K2)*($B$2:$B$16=K3),0),0)),LOOKUP(2,1/(($A$2:$A$16=K2)*($B$2:$B$16=K3)*($C$2:$C$16<=K4)),$G$2:$G$16)))

or if using XL2007 or later:

=IFERROR(LOOKUP(2,1/(($A$2:$A$16=K2)*($B$2:$B$16=K3)*($C$2:$C$16<=K4)),$G$2:$G$16),INDEX($G$2:$G$16,MATCH(1,INDEX(($A$2:$A$16=K2)*($B$2:$B$16=K3),0),0)))

if either of these gives back an error, than the value in K2 and/or K3 don't match up.

You could nest another IFERROR to return a more intelligent result like "No Matches for K2 or K3 found" like this:

=IFERROR(IFERROR(LOOKUP(2,1/(($A$2:$A$16=K2)*($B$2:$B$16=K3)*($C$2:$C$16<=K4)),$G$2:$G$16),INDEX($G$2:$G$16,MATCH(1,INDEX(($A$2:$A$16=K2)*($B$2:$B$16=K3),0),0))),"No Matches for K2 or K3 found")

[nutmeg34]

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.

[nutmeg34]

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.

[nutmeg34]

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

[NBVC]

It is the end of day... can you attach a sample workbook so that it is easier for me to see your problem? Then I will look at either later tonight or tomorrow.

[NBVC]

You haven't sent the example yet.. one thing to check is that your named ranges are all starting and ending at same points and that some aren't offset relative to others... this could throw off results.

[nutmeg34]

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

[nutmeg34]

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

[NBVC]

Attach the files directly here... use the paperclip icon in the reply box.

[nutmeg34]

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

[nutmeg34]

The last zip file works ok for me.

[NBVC]

I have it open now.

Is it the columns with the red arrows that are not working?

As far as I can see, by looking at the first few, it returns the right results.

[nutmeg34]

Here is a snapshot of the behavior:

[NBVC]

I think the problem is in the first INDEX()... it should be referencing the range you want result from on all the formulas, then copy down (no need for CSE entry).

e.g.

INDEX(PowerOutRange,MATCH(1,INDEX((AmbientTempRange=AA9)*(RelHumidRange=AB9),0),0))

so whole formula is:

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

[nutmeg34]

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.

[nutmeg34]

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

Thank you very much.