One way

Assume reference table as posted in cols A to D,
data from row2 down

Inputs
in F2: Air-cooled AC (Equipment type)
in G2: 1 (Tons)

Put in H2, normal ENTER:
=INDEX(B$2:B$100,MATCH(1,INDEX((A$2:A$100=F2)*(G2 =C$2:C$100)*(G2<D$2:D$100),),0))
to return the Min Efficiency. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"thedr9wningman" wrote:
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