looking up two values in two columns and return the values in the third column

[hpn88]

Hi

I'm trying to look up for two values as well but they are not exact values. Specifically, I'm looking to pick a "supply unit" based on the amperage i need at the two voltages that are supplied. So the data in the look up table will be

115V /230V /Model#
0A /10A /1
1.1A /2.1A /2
1.4A /4.1A /3
1.7A /5A /4
1.7A /6.1A /5
5A /2A /6
5A /5A /7
5A /7A /8
10A /0A /9


The lookup value could be something like 4.7A at 115V and 6.1 at 230V. I would need the formula to pick model #8.

I tried something like this so far.
=INDEX(Transformer!N4:P13,MATCH(Z52,OFFSET(Transfo rmer!O4,MATCH(Z51,Transformer!N4:N13,1),0,14),1)+M ATCH(Z51,Transformer!N4:N13,1)+1,3)

Thanks to anyone who attempts to tickle this problem.

[NBVC]

If you can get rid of the A's in each cell, then you could have an simpler formula,

e.g.

=INDEX(Transformer!$P$5:$P$13,MATCH(1,(Transformer!$N$5:$N$13>=Z51)*(Transformer!$O$5:$O$13>=Z52),0))

else you would need to extract the numbers...

e.g.

=INDEX(Transformer!$P$5:$P$13,MATCH(1,(LEFT(Transformer!$N$5:$N$13,FIND("A",Transformer!$N$5:$N$13)-1)+0>=LEFT(Z51,FIND("A",Z51)-1)+0)*(LEFT(Transformer!$O$5:$O$13,FIND("A",Transformer!$O$5:$O$13)-1)+0>=LEFT(Z52,FIND("A",Z52)-1)+0),0))

Note: Either formula needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear around the formula.

[Portuga]

If your model numbers are just numbers with no text strings you could actually use a simple sumproduct formula:

=SUMPRODUCT((N4:N13=Z51)*(O4:O13=Z52)*P4:P13)