possible to use vlookup with multiple lookup values?

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

T. Valko

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

Shane Devenshire

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
 
S

Shig

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

T. Valko

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
 
R

RagDyeR

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
 
T

T. Valko

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

RagDyeR

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

T. Valko

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

AAM

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

T. Valko

Glad it helped. If the Help files were written like that there'd be almost
no need for these ngs.

Thanks for the feedback!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup function to cell containing multiple values 3
somewhat complicated conditional formula 2
cells with one to multiple lines 1
Vlookup cells in combination of two columns (both in the lookup value AND table array) 3
Excel Trying to do a vlookup with a nested right function on the array 3
in an Excel database, comparaison between two columns with "almost"the same names => how to search i 1
Conditional format Column based on Week Date 2
Use Vlookup to store formulas? 6

Top