possible to use vlookup with multiple lookup values?

  • Thread starter Thread starter eggman2001
  • Start date Start date
E

eggman2001

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

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
 
you must have a lot of patience

Yes!
be a damned good typist!
Click to expand...

No. Guess how long it took me to type that. Probably the better part of an
hour! (includes proof reading and typo corrections)
 
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 said:
you must have a lot of patience
Yes!

be a damned good typist!
Click to expand...

No. Guess how long it took me to type that. Probably the better part of an
hour! (includes proof reading and typo corrections)
 
The NGs can really screw up any kind of table
unless you *spend time* on it.
Click to expand...

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.
 
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
 
Glad it helped. If the Help files were written like that there'd be almost
no need for these ngs.

Thanks for the feedback!
 
Back
Top