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.

ryesworld wrote:

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

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


ryesworld wrote:
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!

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.

"Dave Peterson" wrote:

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.

ryesworld wrote:

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 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,

"Pete" wrote:

You can have:

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

or ISNA instead of ISERROR.

Pete

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" wrote:

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


ryesworld wrote:
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!

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


ryesworld wrote:
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" wrote:


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


ryesworld wrote:

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!

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.



ryesworld wrote:

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,

"Pete" wrote:

You can have:

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

or ISNA instead of ISERROR.

Pete



--

Dave Peterson