I struggle with this often, and I've scoured the newsgroups looking for the
answer and I can't find it.

I'm trying to look up the Minimum efficiency of an air conditioner unit
based on two inputs, the Equipment Type and the Minimum Tons.

Whenever I use an index function, I get an error because there are two
values in the minimum tons column. I'm trying to find a way to index, for
example, a 1-ton unit. Match (with -1, or greater than) doesn't seem to be
working.

So, let's use an example: Equipment type=Air-cooled AC. Tonnage= 1.
The criteria are that the minimum tonnage is inclusive (Tons=value) and the
maximum is exclusive (Tons<value).

Equipment type index I can do, but I don't know how to exclude the rest of
the rows for when the minimum/max tons repeat.

1 is less than 5.4 and greater than 0, so it should be the first row of my
table as long as Air-cooled AC is the first column. I should get the answer
10, but I don't know how to do it automatically.

Equipment type Minimum
efficiency Tons min (inclusive) Tons max
Air-cooled AC 10 0.0 5.4
Air-cooled AC 10.3 5.4 11.3
Air-cooled AC 9.7 11.3 20.0
Air-cooled AC 9.5 20.0 63.3
Air-cooled AC 9.2 63.3
[infinity]
Water-cooled AC 12.1 0.0 5.4
Water-cooled AC 11.5 5.4 11.3
Water-cooled AC 11 11.3 20.0
Water-cooled AC 11 20.0 63.3

--
Colourless Green Ideas
transparency | ecology | economy