Was trying to Vlookup two columns on one tab, check it matched the two
columns on current tab and fill in value (Dept # - Acct # - $$$) using
Vlookup. Thought I could do it. Ended up using the SumProduct formula that
worked but do not understand what the heck it is doing with the "*" in the
middle of formula. What was SumProduct doing to bring the correct answer.
Prefer using VLookup but could not get two columns to work..

Maybe you should post the formula in question

an asterisk can indicate multiplication



--
Regards,

Peo Sjoblom



"jlmccabes" wrote in message
...
Was trying to Vlookup two columns on one tab, check it matched the two
columns on current tab and fill in value (Dept # - Acct # - $$$) using
Vlookup. Thought I could do it. Ended up using the SumProduct formula
that
worked but do not understand what the heck it is doing with the "*" in the
middle of formula. What was SumProduct doing to bring the correct answer.
Prefer using VLookup but could not get two columns to work..

The individual components of SP return arrays of TRUE and FALSE. The * is
used to convert these to an array of 1/0 which is used to then get the
corresponding values from the final array.

You could also use

=INDEX(rng1,MATCH(1,(rng2=number)*(rng3="value"),0 ))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jlmccabes" wrote in message
...
Was trying to Vlookup two columns on one tab, check it matched the two
columns on current tab and fill in value (Dept # - Acct # - $$$) using
Vlookup. Thought I could do it. Ended up using the SumProduct formula
that
worked but do not understand what the heck it is doing with the "*" in the
middle of formula. What was SumProduct doing to bring the correct answer.
Prefer using VLookup but could not get two columns to work..

An example of your data and various formulae would help in getting a
meaningful response.

"jlmccabes" wrote:

Was trying to Vlookup two columns on one tab, check it matched the two
columns on current tab and fill in value (Dept # - Acct # - $$$) using
Vlookup. Thought I could do it. Ended up using the SumProduct formula that
worked but do not understand what the heck it is doing with the "*" in the
middle of formula. What was SumProduct doing to bring the correct answer.
Prefer using VLookup but could not get two columns to work..

VLookup is a wonderful feature, but in some aspects, it's limited: first, as
you noted, without some trickery it is limited to finding a match in a single
column, second, it stops looking once a match is found.

The SUMPRODUCT() overcomes both of those limitations. As for the *
(multiply) in the middle, that's often used to 'coerce' the results of a
True/False test into 1 for True, 0 for False. It's actually faster to
replace the * with a comma (,) and in effect the comma acts as a multiplier
in a SUMPRODUCT formula anyhow.

Here's a typical SUMPRODUCT() formula that uses two columns to find the
match and returns the sum of the values of a 3rd column:
=SUMPRODUCT(--(A1:A10=F1),--(B1:B10=G1),C1:C10)
for the two --() portions that actually does the coercion of the TRUE/FALSE
results of the tests within them. A True result, =F1 or =G1, gets turned
into a 1 but a false result ends up as zero. The process goes row by row,
from row 1 to row 10 in the example, and evaluates the PRODUCT of each row.
That is to say, the results of each parameter are multiplied together, AND
the results for each row are SUMmed together for a final result, hence the
name SUMofthePRODUCTs -- SUMPRODUCT().

In our example above we can have either a 1 or a 0 as the result of the
first 2 parameters, if both conditions are true then you get the equivalent
of:
1 * 1 * [value in column C of the row]
which returns the value of column C in the row.
But if either of the first two parameters is false, resulting in a zero,
then the zero in the multiplication sequence results in an evaluation of the
whole row as zero:
1 * 0 * anything = zero similarly 0 * 1 * anything = 0.

Back to the * symbol:
=SUMPRODUCT((A1:A4="george") * (B1:B4="R") * C1:C4)
will provide the same result as:
=SUMPRODUCT(--(A1:A4="george"), --(B1:B4="R"), C1:C4)
although this second format should be slightly faster.

But if you wrote the formula as:
=SUMPRODUCT((A1:A4="george"), (B1:B4="R"), C1:C4)
It doesn't work as you expect at all ... because there's no coercion of the
boolean results into a number that can be used in the multiplication part of
the evaluation.


Hope this helps your understanding a little and hasn't added to the
confusion too much.

"jlmccabes" wrote:

Was trying to Vlookup two columns on one tab, check it matched the two
columns on current tab and fill in value (Dept # - Acct # - $$$) using
Vlookup. Thought I could do it. Ended up using the SumProduct formula that
worked but do not understand what the heck it is doing with the "*" in the
middle of formula. What was SumProduct doing to bring the correct answer.
Prefer using VLookup but could not get two columns to work..

Thank you for the explanation. Was confused by the "*" in the formula -
like am I multiplying here..
At least it makes more sense now,, I think I like the use of a "," - not so
much for the speed but a little easier to follow after the other Excel
formulas. Thank You again - I think I at least understand WHAT it is
doing...

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
VLookup is a wonderful feature, but in some aspects, it's limited: first, as
you noted, without some trickery it is limited to finding a match in a
single
column, second, it stops looking once a match is found.

The SUMPRODUCT() overcomes both of those limitations. As for the *
(multiply) in the middle, that's often used to 'coerce' the results of a
True/False test into 1 for True, 0 for False. It's actually faster to
replace the * with a comma (,) and in effect the comma acts as a multiplier
in a SUMPRODUCT formula anyhow.

Here's a typical SUMPRODUCT() formula that uses two columns to find the
match and returns the sum of the values of a 3rd column:
=SUMPRODUCT(--(A1:A10=F1),--(B1:B10=G1),C1:C10)
for the two --() portions that actually does the coercion of the TRUE/FALSE
results of the tests within them. A True result, =F1 or =G1, gets turned
into a 1 but a false result ends up as zero. The process goes row by row,
from row 1 to row 10 in the example, and evaluates the PRODUCT of each row.
That is to say, the results of each parameter are multiplied together, AND
the results for each row are SUMmed together for a final result, hence the
name SUMofthePRODUCTs -- SUMPRODUCT().

In our example above we can have either a 1 or a 0 as the result of the
first 2 parameters, if both conditions are true then you get the equivalent
of:
1 * 1 * [value in column C of the row]
which returns the value of column C in the row.
But if either of the first two parameters is false, resulting in a zero,
then the zero in the multiplication sequence results in an evaluation of the
whole row as zero:
1 * 0 * anything = zero similarly 0 * 1 * anything = 0.

Back to the * symbol:
=SUMPRODUCT((A1:A4="george") * (B1:B4="R") * C1:C4)
will provide the same result as:
=SUMPRODUCT(--(A1:A4="george"), --(B1:B4="R"), C1:C4)
although this second format should be slightly faster.

But if you wrote the formula as:
=SUMPRODUCT((A1:A4="george"), (B1:B4="R"), C1:C4)
It doesn't work as you expect at all ... because there's no coercion of the
boolean results into a number that can be used in the multiplication part of
the evaluation.


Hope this helps your understanding a little and hasn't added to the
confusion too much.

"jlmccabes" wrote:

Was trying to Vlookup two columns on one tab, check it matched the two
columns on current tab and fill in value (Dept # - Acct # - $$$) using
Vlookup. Thought I could do it. Ended up using the SumProduct formula
that
worked but do not understand what the heck it is doing with the "*" in the
middle of formula. What was SumProduct doing to bring the correct answer.
Prefer using VLookup but could not get two columns to work..