Re: possible to use vlookup with multiple lookup values?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxxxx> wrote in message
news:OaTA2LtEKHA.2832@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message
news:uXI6zmsEKHA.5780@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx> wrote in message
news:e9AA$asEKHA.5780@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message
news:OJj3mYkEKHA.1488@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx> wrote in message
news:5e9bee1f-b24c-48d9-8b4f-45f7d1fe10f4@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.








.

Relevant Pages