How do you do a VLookup with two conditions?

[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!

 

[Dave Peterson]

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.

 

[Roger Govier]

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

 

[Guest]

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.

 

[Pete]

You can have:

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

or ISNA instead of ISERROR.

Pete

 

[Guest]

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,

 

[Guest]

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

 

[Roger Govier]

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

 

[Dave Peterson]

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.