referencing two tables

Hi All,
I have a problem.

I have a formula that selects the lowest connection pipe possible to two pieces of equipment. I need to extend this formula so that it will pick the lowest connection that is compatible with BOTH boxes. For instance:

box one is compatible with a b and c
box two is compatible with a b and d

The lowest connection possible works out to be d, but since this is not compatible with both boxes I need to select b.

Any help much appreciated!

Alastair

Could you give an example of how how you calculation looks like in excel?
Is it 4 columns or 4 cells or ...

Ola

Box 1

Connections Connection capacities
E1 2Mbps
E3 40Mbps
STM-1 150Mbps

Box 2
Connections Capacities
E1 2Mbps
STM-1 150Mbps
STM-4 512Mbps

Box1 feeds box 2 and the output of box 1 is 35Mbps. The lowest connection I would need to send the output is an E3, but since box 2 does not support an E3 I should use an STM-1.

My current formula is
{=MIN(IF(Inputs!$C25:$C30/D25>=1,Inputs!$C25:$C30))}

where inputs!C25:C30 are the connection capacities and D25 is the output. As you can see though this doesn't account for the fact that the connector is not compatible with Box 2.

Ideally I need a formula but code may be ok.

Many thanks for responding!!

Alastair