How do you do a VLookup with two conditions?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All:

I need a formula that will return a cell value if two conditions are met.
Basically a vlookup that has two conditions.
eg.
A formula that would referencing the following table and return a value of
200 when Type = AC and Dia = 150.

Type Dia Length
AC 100 150
AC 150 200
PVC 100 500

Thanks for your help!
 
You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.
 
Hi

One way

With the Type being looked up in F1 and the Diameter being looked up in G1
and the lookup table being in A2:C4 then
=LOOKUP(2,1/((A2:A4=F1)*(B2:B4=G1)),C2:C4)

Regards

Roger Govier
 
This works, Thank you.

However, if one of the conditions is not met, the returned value is #N/A.
In this case, is there a way to make the formula return a value of zero, or a
dash, or nothing at all (so that I can sum the values that are returned)?

Thanks.
 
You can have:

= IF ( ISERROR (lookup formula), "", lookup formula),

or ISNA instead of ISERROR.

Pete
 
Hi, I have two formulas that work (below) when both conditions are met, but
both return "#N/A" if one of the conditions is not met.

=LOOKUP(2,1/(($A$1:$A$100=N$1)*($B$1:$B$100=$M2)),$F$1:$F$100)
=INDEX($F$1:$F$100,MATCH(1,($A$1:$A$100=N$1)*($B$1:$B$100=$M2),0))

$A$1:$A$100 is the Type column, N$1 is the type to match
$B$1:$B$100 is the Diameter column, $M2 is the diameter to match
$F$1:$F$100 is the Length column

How would I incorporate your formula into either of these formulas?

Thanks,
 
Hi Rodger, Thanks for your formula, this is how it fits into my spreadsheet...
=LOOKUP(2,1/(($A$1:$A$100=N$1)*($B$1:$B$100=$M2)),$F$1:$F$100)

$A$1:$A$100 is the Type column, N$1 is the type to match
$B$1:$B$100 is the Diameter column, $M2 is the diameter to match
$F$1:$F$100 is the Length column

This formula works if both conditions are met, but returns "#N/A" if one of
the conditions is not met. Any idea how to return a zero or dash or
something that can be autosumed?

Thanks,
Ryan
 
Hi Ryan

try
=IF(ISERROR(LOOKUP(2,1/(($A$1:$A$100=N$1)*($B$1:$B$100=$M2)),$F$1:$F$100)),0,
LOOKUP(2,1/(($A$1:$A$100=N$1)*($B$1:$B$100=$M2)),$F$1:$F$100))

Regards

Roger Govier
 
I would use a few columns.

Put your formulas that do the real work in one, and then check it for an error
in another.

=if(iserror(b2),0,b2)
if B2 contained one of those formulas

Hide the columns that return the errors if you don't want to see them.
 
Back
Top