possible to use vlookup with multiple lookup values?

I'd like to use two columns of data to look up values.

In my main table, I have A1=foo, B1=bar
In my look up table, I have A1=foo, B1=bar and C1=123.

I'd like to create a vlookup that will populate C1 in my main table
based on the contents of cells A1 and B1.

Is this possible?
0
sodani (28)
7/28/2009 5:05:57 PM
excel 39879 articles. 2 followers. Follow

10 Replies
690 Views

Similar Articles

[PageSpeed] 56

Try one of these:

If foo and bar will be a *unique combination* and the value to be returned 
is numeric:

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10=B1),Sheet2!C1:C10)

Or, this generic array entered** version (returns both text and numbers):

=INDEX(Sheet2!C1:C10,MATCH(1,(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),0))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"eggman2001" <sodani@gmail.com> wrote in message 
news:81dfeaf9-2227-4db3-ac33-b724c6546ba4@d23g2000vbm.googlegroups.com...
> I'd like to use two columns of data to look up values.
>
> In my main table, I have A1=foo, B1=bar
> In my look up table, I have A1=foo, B1=bar and C1=123.
>
> I'd like to create a vlookup that will populate C1 in my main table
> based on the contents of cells A1 and B1.
>
> Is this possible? 


0
biffinpitt (3172)
7/28/2009 5:41:19 PM
Hi,

Here is one way:

=SUMPRODUCT(--(A1:A10&B1:B10=F1&G1),C1:C10)

I am using F1 and G1 to contain Foo and Bar

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"eggman2001" wrote:

> I'd like to use two columns of data to look up values.
> 
> In my main table, I have A1=foo, B1=bar
> In my look up table, I have A1=foo, B1=bar and C1=123.
> 
> I'd like to create a vlookup that will populate C1 in my main table
> based on the contents of cells A1 and B1.
> 
> Is this possible?
> 
0
7/29/2009 6:00:01 AM
I'm having trouble understanding how the SUMPRODUCT function works in
this case even after doing some reading up on it. I also don't know
what the "--" means....

That said, I figured out that I can just concatenate the values in my
two columns to make a third, unique value, which I do the vlookup on.
0
sodani (28)
7/31/2009 10:15:01 PM
Let's see how the SUMPRODUCT formula works using this sample data:

...........A..........B..........C
1.......Fe.........Fi.........10
2.......Fo........Fum......22
3.......Foo......Bar.......17
4.......Foo......Gee......42
5.......Bar.......Fly.......19

You want to lookup Foo and Bar and return the corresponding numeric value 
from col C.

As long as the combination of Foo and Bar are unique we can use the 
SUMPRODUCT function to get the result.

=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))

Result = 17

Here's how it works...

SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, we're 
going to multiply some things and then get the SUM of that multiplication.

Each of these expressions will return an array of either TRUE (T) or FALSE 
(F):

(A1:A5="Foo")
(B1:B5="Bar")

Fe = Foo = F
Fo = Foo = F
Foo = Foo = T
Foo = Foo = T
Bar = Foo = F

Fi = Bar = F
Fum = Bar = F
Bar = Bar = T
Gee = Bar = F
Fly = Bar = F

SUMPRODUCT works with numbers so we need to convert those logical values, 
TRUE and FALSE, to numbers. One way to do that is to use the double unary 
"--". It coerces TRUE to numeric 1 and FALSE to numeric 0:

--TRUE = 1
--FALSE = 0

--(A1:A5="Foo")
--(B1:B5="Bar")

--(Fe = Foo) = F = 0
--(Fo = Foo) = F = 0
--(Foo = Foo) = T = 1
--(Foo = Foo) = T = 1
--(Bar = Foo) = F = 0

--(Fi = Bar) = F = 0
--(Fum = Bar) = F = 0
--(Bar = Bar)  = T = 1
--(Gee = Bar) = F = 0
--(Fly = Bar) = F = 0

Now, here's where the multiplication takes place.

We coerced the logical test arrays to numbers and the data in col C is 
already numbers so now these 3 arrays are multiplied together:

0 * 0 * 10 = 0
0 * 0 * 22 = 0
1 * 1 * 17 = 17
1 * 0 * 42 = 0
0 * 0 * 19 = 0

We have the results of the multiplication (PRODUCTS) so we just add (SUM) 
them up:

=SUMPRODUCT({0;0;17;0;0}) = 17

So:

=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))

Result = 17


exp101
-- 
Biff
Microsoft Excel MVP


"Shig" <sodani@gmail.com> wrote in message 
news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
> I'm having trouble understanding how the SUMPRODUCT function works in
> this case even after doing some reading up on it. I also don't know
> what the "--" means....
>
> That said, I figured out that I can just concatenate the values in my
> two columns to make a third, unique value, which I do the vlookup on. 


0
biffinpitt (3172)
8/1/2009 1:19:41 AM
WOW ...
With such a comprehensive explanation, you must have a lot of patience, or 
be a damned good typist!<bg>
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:OJj3mYkEKHA.1488@TK2MSFTNGP03.phx.gbl...
Let's see how the SUMPRODUCT formula works using this sample data:

...........A..........B..........C
1.......Fe.........Fi.........10
2.......Fo........Fum......22
3.......Foo......Bar.......17
4.......Foo......Gee......42
5.......Bar.......Fly.......19

You want to lookup Foo and Bar and return the corresponding numeric value
from col C.

As long as the combination of Foo and Bar are unique we can use the
SUMPRODUCT function to get the result.

=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))

Result = 17

Here's how it works...

SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, we're
going to multiply some things and then get the SUM of that multiplication.

Each of these expressions will return an array of either TRUE (T) or FALSE
(F):

(A1:A5="Foo")
(B1:B5="Bar")

Fe = Foo = F
Fo = Foo = F
Foo = Foo = T
Foo = Foo = T
Bar = Foo = F

Fi = Bar = F
Fum = Bar = F
Bar = Bar = T
Gee = Bar = F
Fly = Bar = F

SUMPRODUCT works with numbers so we need to convert those logical values,
TRUE and FALSE, to numbers. One way to do that is to use the double unary
"--". It coerces TRUE to numeric 1 and FALSE to numeric 0:

--TRUE = 1
--FALSE = 0

--(A1:A5="Foo")
--(B1:B5="Bar")

--(Fe = Foo) = F = 0
--(Fo = Foo) = F = 0
--(Foo = Foo) = T = 1
--(Foo = Foo) = T = 1
--(Bar = Foo) = F = 0

--(Fi = Bar) = F = 0
--(Fum = Bar) = F = 0
--(Bar = Bar)  = T = 1
--(Gee = Bar) = F = 0
--(Fly = Bar) = F = 0

Now, here's where the multiplication takes place.

We coerced the logical test arrays to numbers and the data in col C is
already numbers so now these 3 arrays are multiplied together:

0 * 0 * 10 = 0
0 * 0 * 22 = 0
1 * 1 * 17 = 17
1 * 0 * 42 = 0
0 * 0 * 19 = 0

We have the results of the multiplication (PRODUCTS) so we just add (SUM)
them up:

=SUMPRODUCT({0;0;17;0;0}) = 17

So:

=SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))

Result = 17


exp101
-- 
Biff
Microsoft Excel MVP


"Shig" <sodani@gmail.com> wrote in message
news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
> I'm having trouble understanding how the SUMPRODUCT function works in
> this case even after doing some reading up on it. I also don't know
> what the "--" means....
>
> That said, I figured out that I can just concatenate the values in my
> two columns to make a third, unique value, which I do the vlookup on.



0
ragdyer1 (4060)
8/1/2009 4:36:09 PM
>you must have a lot of patience

Yes!

>be a damned good typist!

No. Guess how long it took me to type that. Probably the better part of an 
hour! (includes proof reading and typo corrections)

-- 
Biff
Microsoft Excel MVP


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
news:e9AA$asEKHA.5780@TK2MSFTNGP03.phx.gbl...
> WOW ...
> With such a comprehensive explanation, you must have a lot of patience, or
> be a damned good typist!<bg>
> -- 
>
> Regards,
>
> RD
> -----------------------------------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> -----------------------------------------------------------------------------------------------
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:OJj3mYkEKHA.1488@TK2MSFTNGP03.phx.gbl...
> Let's see how the SUMPRODUCT formula works using this sample data:
>
> ..........A..........B..........C
> 1.......Fe.........Fi.........10
> 2.......Fo........Fum......22
> 3.......Foo......Bar.......17
> 4.......Foo......Gee......42
> 5.......Bar.......Fly.......19
>
> You want to lookup Foo and Bar and return the corresponding numeric value
> from col C.
>
> As long as the combination of Foo and Bar are unique we can use the
> SUMPRODUCT function to get the result.
>
> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>
> Result = 17
>
> Here's how it works...
>
> SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So, 
> we're
> going to multiply some things and then get the SUM of that multiplication.
>
> Each of these expressions will return an array of either TRUE (T) or FALSE
> (F):
>
> (A1:A5="Foo")
> (B1:B5="Bar")
>
> Fe = Foo = F
> Fo = Foo = F
> Foo = Foo = T
> Foo = Foo = T
> Bar = Foo = F
>
> Fi = Bar = F
> Fum = Bar = F
> Bar = Bar = T
> Gee = Bar = F
> Fly = Bar = F
>
> SUMPRODUCT works with numbers so we need to convert those logical values,
> TRUE and FALSE, to numbers. One way to do that is to use the double unary
> "--". It coerces TRUE to numeric 1 and FALSE to numeric 0:
>
> --TRUE = 1
> --FALSE = 0
>
> --(A1:A5="Foo")
> --(B1:B5="Bar")
>
> --(Fe = Foo) = F = 0
> --(Fo = Foo) = F = 0
> --(Foo = Foo) = T = 1
> --(Foo = Foo) = T = 1
> --(Bar = Foo) = F = 0
>
> --(Fi = Bar) = F = 0
> --(Fum = Bar) = F = 0
> --(Bar = Bar)  = T = 1
> --(Gee = Bar) = F = 0
> --(Fly = Bar) = F = 0
>
> Now, here's where the multiplication takes place.
>
> We coerced the logical test arrays to numbers and the data in col C is
> already numbers so now these 3 arrays are multiplied together:
>
> 0 * 0 * 10 = 0
> 0 * 0 * 22 = 0
> 1 * 1 * 17 = 17
> 1 * 0 * 42 = 0
> 0 * 0 * 19 = 0
>
> We have the results of the multiplication (PRODUCTS) so we just add (SUM)
> them up:
>
> =SUMPRODUCT({0;0;17;0;0}) = 17
>
> So:
>
> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>
> Result = 17
>
>
> exp101
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Shig" <sodani@gmail.com> wrote in message
> news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
>> I'm having trouble understanding how the SUMPRODUCT function works in
>> this case even after doing some reading up on it. I also don't know
>> what the "--" means....
>>
>> That said, I figured out that I can just concatenate the values in my
>> two columns to make a third, unique value, which I do the vlookup on.
>
>
> 


0
biffinpitt (3172)
8/1/2009 5:01:22 PM
That's what I was actually referring to ... the time involved in putting it 
into a legible, understandable format.

The NGs can really screw up any kind of table ... unless you *spend time* on 
it.

BUT, then again ... you retired guys have all the time!<bg>
-- 

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:uXI6zmsEKHA.5780@TK2MSFTNGP03.phx.gbl...
>you must have a lot of patience

Yes!

>be a damned good typist!

No. Guess how long it took me to type that. Probably the better part of an
hour! (includes proof reading and typo corrections)

-- 
Biff
Microsoft Excel MVP


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:e9AA$asEKHA.5780@TK2MSFTNGP03.phx.gbl...
> WOW ...
> With such a comprehensive explanation, you must have a lot of patience, or
> be a damned good typist!<bg>
> -- 
>
> Regards,
>
> RD
> -----------------------------------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> -----------------------------------------------------------------------------------------------
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:OJj3mYkEKHA.1488@TK2MSFTNGP03.phx.gbl...
> Let's see how the SUMPRODUCT formula works using this sample data:
>
> ..........A..........B..........C
> 1.......Fe.........Fi.........10
> 2.......Fo........Fum......22
> 3.......Foo......Bar.......17
> 4.......Foo......Gee......42
> 5.......Bar.......Fly.......19
>
> You want to lookup Foo and Bar and return the corresponding numeric value
> from col C.
>
> As long as the combination of Foo and Bar are unique we can use the
> SUMPRODUCT function to get the result.
>
> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>
> Result = 17
>
> Here's how it works...
>
> SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So,
> we're
> going to multiply some things and then get the SUM of that multiplication.
>
> Each of these expressions will return an array of either TRUE (T) or FALSE
> (F):
>
> (A1:A5="Foo")
> (B1:B5="Bar")
>
> Fe = Foo = F
> Fo = Foo = F
> Foo = Foo = T
> Foo = Foo = T
> Bar = Foo = F
>
> Fi = Bar = F
> Fum = Bar = F
> Bar = Bar = T
> Gee = Bar = F
> Fly = Bar = F
>
> SUMPRODUCT works with numbers so we need to convert those logical values,
> TRUE and FALSE, to numbers. One way to do that is to use the double unary
> "--". It coerces TRUE to numeric 1 and FALSE to numeric 0:
>
> --TRUE = 1
> --FALSE = 0
>
> --(A1:A5="Foo")
> --(B1:B5="Bar")
>
> --(Fe = Foo) = F = 0
> --(Fo = Foo) = F = 0
> --(Foo = Foo) = T = 1
> --(Foo = Foo) = T = 1
> --(Bar = Foo) = F = 0
>
> --(Fi = Bar) = F = 0
> --(Fum = Bar) = F = 0
> --(Bar = Bar)  = T = 1
> --(Gee = Bar) = F = 0
> --(Fly = Bar) = F = 0
>
> Now, here's where the multiplication takes place.
>
> We coerced the logical test arrays to numbers and the data in col C is
> already numbers so now these 3 arrays are multiplied together:
>
> 0 * 0 * 10 = 0
> 0 * 0 * 22 = 0
> 1 * 1 * 17 = 17
> 1 * 0 * 42 = 0
> 0 * 0 * 19 = 0
>
> We have the results of the multiplication (PRODUCTS) so we just add (SUM)
> them up:
>
> =SUMPRODUCT({0;0;17;0;0}) = 17
>
> So:
>
> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>
> Result = 17
>
>
> exp101
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Shig" <sodani@gmail.com> wrote in message
> news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
>> I'm having trouble understanding how the SUMPRODUCT function works in
>> this case even after doing some reading up on it. I also don't know
>> what the "--" means....
>>
>> That said, I figured out that I can just concatenate the values in my
>> two columns to make a third, unique value, which I do the vlookup on.
>
>
>



0
ragdyer1 (4060)
8/1/2009 6:03:35 PM
>The NGs can really screw up any kind of table
>unless you *spend time* on it.

That's for sure.

It'd be easier to understand if you could put the multiple array comparisons 
side by side but you never know how much "line width" you get before line 
wrap hoses everything and you end up with an unreadable mess.

I don't mind. I like explaining things.

-- 
Biff
Microsoft Excel MVP


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
news:OaTA2LtEKHA.2832@TK2MSFTNGP03.phx.gbl...
> That's what I was actually referring to ... the time involved in putting 
> it
> into a legible, understandable format.
>
> The NGs can really screw up any kind of table ... unless you *spend time* 
> on
> it.
>
> BUT, then again ... you retired guys have all the time!<bg>
> -- 
>
> Regards,
>
> RD
> -----------------------------------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> -----------------------------------------------------------------------------------------------
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:uXI6zmsEKHA.5780@TK2MSFTNGP03.phx.gbl...
>>you must have a lot of patience
>
> Yes!
>
>>be a damned good typist!
>
> No. Guess how long it took me to type that. Probably the better part of an
> hour! (includes proof reading and typo corrections)
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:e9AA$asEKHA.5780@TK2MSFTNGP03.phx.gbl...
>> WOW ...
>> With such a comprehensive explanation, you must have a lot of patience, 
>> or
>> be a damned good typist!<bg>
>> -- 
>>
>> Regards,
>>
>> RD
>> -----------------------------------------------------------------------------------------------
>> Please keep all correspondence within the Group, so all may benefit !
>> -----------------------------------------------------------------------------------------------
>>
>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>> news:OJj3mYkEKHA.1488@TK2MSFTNGP03.phx.gbl...
>> Let's see how the SUMPRODUCT formula works using this sample data:
>>
>> ..........A..........B..........C
>> 1.......Fe.........Fi.........10
>> 2.......Fo........Fum......22
>> 3.......Foo......Bar.......17
>> 4.......Foo......Gee......42
>> 5.......Bar.......Fly.......19
>>
>> You want to lookup Foo and Bar and return the corresponding numeric value
>> from col C.
>>
>> As long as the combination of Foo and Bar are unique we can use the
>> SUMPRODUCT function to get the result.
>>
>> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>>
>> Result = 17
>>
>> Here's how it works...
>>
>> SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So,
>> we're
>> going to multiply some things and then get the SUM of that 
>> multiplication.
>>
>> Each of these expressions will return an array of either TRUE (T) or 
>> FALSE
>> (F):
>>
>> (A1:A5="Foo")
>> (B1:B5="Bar")
>>
>> Fe = Foo = F
>> Fo = Foo = F
>> Foo = Foo = T
>> Foo = Foo = T
>> Bar = Foo = F
>>
>> Fi = Bar = F
>> Fum = Bar = F
>> Bar = Bar = T
>> Gee = Bar = F
>> Fly = Bar = F
>>
>> SUMPRODUCT works with numbers so we need to convert those logical values,
>> TRUE and FALSE, to numbers. One way to do that is to use the double unary
>> "--". It coerces TRUE to numeric 1 and FALSE to numeric 0:
>>
>> --TRUE = 1
>> --FALSE = 0
>>
>> --(A1:A5="Foo")
>> --(B1:B5="Bar")
>>
>> --(Fe = Foo) = F = 0
>> --(Fo = Foo) = F = 0
>> --(Foo = Foo) = T = 1
>> --(Foo = Foo) = T = 1
>> --(Bar = Foo) = F = 0
>>
>> --(Fi = Bar) = F = 0
>> --(Fum = Bar) = F = 0
>> --(Bar = Bar)  = T = 1
>> --(Gee = Bar) = F = 0
>> --(Fly = Bar) = F = 0
>>
>> Now, here's where the multiplication takes place.
>>
>> We coerced the logical test arrays to numbers and the data in col C is
>> already numbers so now these 3 arrays are multiplied together:
>>
>> 0 * 0 * 10 = 0
>> 0 * 0 * 22 = 0
>> 1 * 1 * 17 = 17
>> 1 * 0 * 42 = 0
>> 0 * 0 * 19 = 0
>>
>> We have the results of the multiplication (PRODUCTS) so we just add (SUM)
>> them up:
>>
>> =SUMPRODUCT({0;0;17;0;0}) = 17
>>
>> So:
>>
>> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>>
>> Result = 17
>>
>>
>> exp101
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Shig" <sodani@gmail.com> wrote in message
>> news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
>>> I'm having trouble understanding how the SUMPRODUCT function works in
>>> this case even after doing some reading up on it. I also don't know
>>> what the "--" means....
>>>
>>> That said, I figured out that I can just concatenate the values in my
>>> two columns to make a third, unique value, which I do the vlookup on.
>>
>>
>>
>
>
> 


0
biffinpitt (3172)
8/1/2009 6:41:25 PM
> I don't mind. I like explaining things

Lucky for us!  Thanks for that, it gave me a much simpler angle than what I
had come up with to do something similar.

Andy


"T. Valko" <biffinpitt@comcast.net> wrote in message
news:%23cP7tetEKHA.4280@TK2MSFTNGP05.phx.gbl...
> >The NGs can really screw up any kind of table
>>unless you *spend time* on it.
>
> That's for sure.
>
> It'd be easier to understand if you could put the multiple array
> comparisons side by side but you never know how much "line width" you get
> before line wrap hoses everything and you end up with an unreadable mess.
>
> I don't mind. I like explaining things.
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:OaTA2LtEKHA.2832@TK2MSFTNGP03.phx.gbl...
>> That's what I was actually referring to ... the time involved in putting
>> it
>> into a legible, understandable format.
>>
>> The NGs can really screw up any kind of table ... unless you *spend time*
>> on
>> it.
>>
>> BUT, then again ... you retired guys have all the time!<bg>
>> -- 
>>
>> Regards,
>>
>> RD
>> -----------------------------------------------------------------------------------------------
>> Please keep all correspondence within the Group, so all may benefit !
>> -----------------------------------------------------------------------------------------------
>>
>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>> news:uXI6zmsEKHA.5780@TK2MSFTNGP03.phx.gbl...
>>>you must have a lot of patience
>>
>> Yes!
>>
>>>be a damned good typist!
>>
>> No. Guess how long it took me to type that. Probably the better part of
>> an
>> hour! (includes proof reading and typo corrections)
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
>> news:e9AA$asEKHA.5780@TK2MSFTNGP03.phx.gbl...
>>> WOW ...
>>> With such a comprehensive explanation, you must have a lot of patience,
>>> or
>>> be a damned good typist!<bg>
>>> -- 
>>>
>>> Regards,
>>>
>>> RD
>>> -----------------------------------------------------------------------------------------------
>>> Please keep all correspondence within the Group, so all may benefit !
>>> -----------------------------------------------------------------------------------------------
>>>
>>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>>> news:OJj3mYkEKHA.1488@TK2MSFTNGP03.phx.gbl...
>>> Let's see how the SUMPRODUCT formula works using this sample data:
>>>
>>> ..........A..........B..........C
>>> 1.......Fe.........Fi.........10
>>> 2.......Fo........Fum......22
>>> 3.......Foo......Bar.......17
>>> 4.......Foo......Gee......42
>>> 5.......Bar.......Fly.......19
>>>
>>> You want to lookup Foo and Bar and return the corresponding numeric
>>> value
>>> from col C.
>>>
>>> As long as the combination of Foo and Bar are unique we can use the
>>> SUMPRODUCT function to get the result.
>>>
>>> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>>>
>>> Result = 17
>>>
>>> Here's how it works...
>>>
>>> SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So,
>>> we're
>>> going to multiply some things and then get the SUM of that
>>> multiplication.
>>>
>>> Each of these expressions will return an array of either TRUE (T) or
>>> FALSE
>>> (F):
>>>
>>> (A1:A5="Foo")
>>> (B1:B5="Bar")
>>>
>>> Fe = Foo = F
>>> Fo = Foo = F
>>> Foo = Foo = T
>>> Foo = Foo = T
>>> Bar = Foo = F
>>>
>>> Fi = Bar = F
>>> Fum = Bar = F
>>> Bar = Bar = T
>>> Gee = Bar = F
>>> Fly = Bar = F
>>>
>>> SUMPRODUCT works with numbers so we need to convert those logical
>>> values,
>>> TRUE and FALSE, to numbers. One way to do that is to use the double
>>> unary
>>> "--". It coerces TRUE to numeric 1 and FALSE to numeric 0:
>>>
>>> --TRUE = 1
>>> --FALSE = 0
>>>
>>> --(A1:A5="Foo")
>>> --(B1:B5="Bar")
>>>
>>> --(Fe = Foo) = F = 0
>>> --(Fo = Foo) = F = 0
>>> --(Foo = Foo) = T = 1
>>> --(Foo = Foo) = T = 1
>>> --(Bar = Foo) = F = 0
>>>
>>> --(Fi = Bar) = F = 0
>>> --(Fum = Bar) = F = 0
>>> --(Bar = Bar)  = T = 1
>>> --(Gee = Bar) = F = 0
>>> --(Fly = Bar) = F = 0
>>>
>>> Now, here's where the multiplication takes place.
>>>
>>> We coerced the logical test arrays to numbers and the data in col C is
>>> already numbers so now these 3 arrays are multiplied together:
>>>
>>> 0 * 0 * 10 = 0
>>> 0 * 0 * 22 = 0
>>> 1 * 1 * 17 = 17
>>> 1 * 0 * 42 = 0
>>> 0 * 0 * 19 = 0
>>>
>>> We have the results of the multiplication (PRODUCTS) so we just add
>>> (SUM)
>>> them up:
>>>
>>> =SUMPRODUCT({0;0;17;0;0}) = 17
>>>
>>> So:
>>>
>>> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>>>
>>> Result = 17
>>>
>>>
>>> exp101
>>> -- 
>>> Biff
>>> Microsoft Excel MVP
>>>
>>>
>>> "Shig" <sodani@gmail.com> wrote in message
>>> news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
>>>> I'm having trouble understanding how the SUMPRODUCT function works in
>>>> this case even after doing some reading up on it. I also don't know
>>>> what the "--" means....
>>>>
>>>> That said, I figured out that I can just concatenate the values in my
>>>> two columns to make a third, unique value, which I do the vlookup on.
>>>
>>>
>>>
>>
>>
>>
>
>



0
8/2/2009 2:20:44 AM
Glad it helped. If the Help files were written like that there'd be almost 
no need for these ngs.

Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"AAM" <noaddress@nowhere.com> wrote in message 
news:uD7%23gfxEKHA.3708@TK2MSFTNGP02.phx.gbl...
>> I don't mind. I like explaining things
>
> Lucky for us!  Thanks for that, it gave me a much simpler angle than what 
> I
> had come up with to do something similar.
>
> Andy
>
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:%23cP7tetEKHA.4280@TK2MSFTNGP05.phx.gbl...
>> >The NGs can really screw up any kind of table
>>>unless you *spend time* on it.
>>
>> That's for sure.
>>
>> It'd be easier to understand if you could put the multiple array
>> comparisons side by side but you never know how much "line width" you get
>> before line wrap hoses everything and you end up with an unreadable mess.
>>
>> I don't mind. I like explaining things.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
>> news:OaTA2LtEKHA.2832@TK2MSFTNGP03.phx.gbl...
>>> That's what I was actually referring to ... the time involved in putting
>>> it
>>> into a legible, understandable format.
>>>
>>> The NGs can really screw up any kind of table ... unless you *spend 
>>> time*
>>> on
>>> it.
>>>
>>> BUT, then again ... you retired guys have all the time!<bg>
>>> -- 
>>>
>>> Regards,
>>>
>>> RD
>>> -----------------------------------------------------------------------------------------------
>>> Please keep all correspondence within the Group, so all may benefit !
>>> -----------------------------------------------------------------------------------------------
>>>
>>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>>> news:uXI6zmsEKHA.5780@TK2MSFTNGP03.phx.gbl...
>>>>you must have a lot of patience
>>>
>>> Yes!
>>>
>>>>be a damned good typist!
>>>
>>> No. Guess how long it took me to type that. Probably the better part of
>>> an
>>> hour! (includes proof reading and typo corrections)
>>>
>>> -- 
>>> Biff
>>> Microsoft Excel MVP
>>>
>>>
>>> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
>>> news:e9AA$asEKHA.5780@TK2MSFTNGP03.phx.gbl...
>>>> WOW ...
>>>> With such a comprehensive explanation, you must have a lot of patience,
>>>> or
>>>> be a damned good typist!<bg>
>>>> -- 
>>>>
>>>> Regards,
>>>>
>>>> RD
>>>> -----------------------------------------------------------------------------------------------
>>>> Please keep all correspondence within the Group, so all may benefit !
>>>> -----------------------------------------------------------------------------------------------
>>>>
>>>> "T. Valko" <biffinpitt@comcast.net> wrote in message
>>>> news:OJj3mYkEKHA.1488@TK2MSFTNGP03.phx.gbl...
>>>> Let's see how the SUMPRODUCT formula works using this sample data:
>>>>
>>>> ..........A..........B..........C
>>>> 1.......Fe.........Fi.........10
>>>> 2.......Fo........Fum......22
>>>> 3.......Foo......Bar.......17
>>>> 4.......Foo......Gee......42
>>>> 5.......Bar.......Fly.......19
>>>>
>>>> You want to lookup Foo and Bar and return the corresponding numeric
>>>> value
>>>> from col C.
>>>>
>>>> As long as the combination of Foo and Bar are unique we can use the
>>>> SUMPRODUCT function to get the result.
>>>>
>>>> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>>>>
>>>> Result = 17
>>>>
>>>> Here's how it works...
>>>>
>>>> SUMPRODUCT means the SUM of PRODUCTS. PRODUCTS means to multiply. So,
>>>> we're
>>>> going to multiply some things and then get the SUM of that
>>>> multiplication.
>>>>
>>>> Each of these expressions will return an array of either TRUE (T) or
>>>> FALSE
>>>> (F):
>>>>
>>>> (A1:A5="Foo")
>>>> (B1:B5="Bar")
>>>>
>>>> Fe = Foo = F
>>>> Fo = Foo = F
>>>> Foo = Foo = T
>>>> Foo = Foo = T
>>>> Bar = Foo = F
>>>>
>>>> Fi = Bar = F
>>>> Fum = Bar = F
>>>> Bar = Bar = T
>>>> Gee = Bar = F
>>>> Fly = Bar = F
>>>>
>>>> SUMPRODUCT works with numbers so we need to convert those logical
>>>> values,
>>>> TRUE and FALSE, to numbers. One way to do that is to use the double
>>>> unary
>>>> "--". It coerces TRUE to numeric 1 and FALSE to numeric 0:
>>>>
>>>> --TRUE = 1
>>>> --FALSE = 0
>>>>
>>>> --(A1:A5="Foo")
>>>> --(B1:B5="Bar")
>>>>
>>>> --(Fe = Foo) = F = 0
>>>> --(Fo = Foo) = F = 0
>>>> --(Foo = Foo) = T = 1
>>>> --(Foo = Foo) = T = 1
>>>> --(Bar = Foo) = F = 0
>>>>
>>>> --(Fi = Bar) = F = 0
>>>> --(Fum = Bar) = F = 0
>>>> --(Bar = Bar)  = T = 1
>>>> --(Gee = Bar) = F = 0
>>>> --(Fly = Bar) = F = 0
>>>>
>>>> Now, here's where the multiplication takes place.
>>>>
>>>> We coerced the logical test arrays to numbers and the data in col C is
>>>> already numbers so now these 3 arrays are multiplied together:
>>>>
>>>> 0 * 0 * 10 = 0
>>>> 0 * 0 * 22 = 0
>>>> 1 * 1 * 17 = 17
>>>> 1 * 0 * 42 = 0
>>>> 0 * 0 * 19 = 0
>>>>
>>>> We have the results of the multiplication (PRODUCTS) so we just add
>>>> (SUM)
>>>> them up:
>>>>
>>>> =SUMPRODUCT({0;0;17;0;0}) = 17
>>>>
>>>> So:
>>>>
>>>> =SUMPRODUCT(--(A1:A5="Foo"),--(B1:B5="Bar"),C1:C5))
>>>>
>>>> Result = 17
>>>>
>>>>
>>>> exp101
>>>> -- 
>>>> Biff
>>>> Microsoft Excel MVP
>>>>
>>>>
>>>> "Shig" <sodani@gmail.com> wrote in message
>>>> news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@l35g2000vba.googlegroups.com...
>>>>> I'm having trouble understanding how the SUMPRODUCT function works in
>>>>> this case even after doing some reading up on it. I also don't know
>>>>> what the "--" means....
>>>>>
>>>>> That said, I figured out that I can just concatenate the values in my
>>>>> two columns to make a third, unique value, which I do the vlookup on.
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
> 


0
biffinpitt (3172)
8/2/2009 2:54:41 AM
Reply:

Similar Artilces:

multiple flyers per page?
Hello. I need to make a custom template with publisher. It will be one 8 1/2 by 11 paper divided horizontally into three parts so that there are three fliers that can be printed. each flier needs to be double printed so I need to make it a double sided project like a postcard or brochure layout. I can figure out text and image stuff. But I don't know how to make a basic custom project that is divided into three panels horizontally (so 8 inches by 3.83 inches roughly) Also an additional customization would divide each 3.83 by 8 inch flyer into two parts so one 8 by 11 page would ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Vlookup returns "0"
I am using the following formula =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" , VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Multiple Simultaneous Outlook Sessions
Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous Outlook Sessions open at one time? If so, how is it setup to send and receive from the same profile I would choose and save the replies back into the same profile? thx "Art" <Art@discussions.microsoft.com> wrote in message news:433A0AAA-9DB0-4411-954F-5165374B049D@microsoft.com... > Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous > Outlook > Sessions open at one time? If so, how is it setup to send and receive from > the same profile I would choose and sa...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

I want to add a mail account without setting a SMTP server. Is it possible?
I want to add a second mail account that is outside my company. Since the company firewall does not allow us to connect to SMTP-servers I want to add this account without setting the SMTP propertiy. I cannot specify the company server as it is an Exchange server and the outside one an IMAP one. Currently I have specified the outside SMTP server since outook does not allow me to add an account wihtout setting this. I keep getting annoying error messages as my computer can't connect to the outside SMTP. Any one know a way around this? cheers, mortb The company server has SMTP enabled, u...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...