How do you do a VLookup with two conditions?

[ryesworld]

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.

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

[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


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

[ryesworld]

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
>

[Pete]

You can have:

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

or ISNA instead of ISERROR.

Pete

[ryesworld]

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

[ryesworld]

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

[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


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

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



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