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

**hpn88** · 04-25-2008, 09:30 AM

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** · 04-25-2008, 09:48 AM

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** · 04-25-2008, 11:28 PM

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)

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

Thread Tools

Display

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions