Hard to tell where your columns end/begin. Follow this general syntaxarray
entered** :

=INDEX(result_range,MATCH(1,(equip_range="equip_ty pe")*(min_range<=variable)*(max_range=variable),0 ))

Better if you use cells to hold all the criteria:

=INDEX(result_range,MATCH(1,(equip_range=A1)*(min_ range<=B1)*(max_range=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"thedr9wningman" wrote in message
...
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