Vlookup for multiple values and return to one desired value
 

I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

One way:

=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

Better to use cells to hold the criteria:

F1 = Jan
G1 = John

=SUMPRODUCT(--(A1:A5=F1),--(B1:B5=G1),C1:C5)

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the
desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

try this

=INDEX(C1:C5,MATCH("jan",A1:A5,0)*MATCH("john",B1: B5,0),0)



On Sep 23, 1:18*pm, nsd wrote:
I have some data as under

A * * * * * *B * * * * * * C * * * * *D
Jan * * * *John * * * * 2 * * * * * 3
Feb * * * Peter * * * *3 * * * * * *4
Mar * * * John * * * * 4 * * * * * *2
Apr * * * *Dan * * * * *2 * * * * * *3
May * * * John * * * * *3 * * * * * 2

What should I do to Vlookup for John for the month of Jan to get the desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

thanks. But I wanted to check on a different issue. Sorry if my question
misled.
I wanted to do a Vlookup for John's no. (which is c) in the month of Jan.
Hence I wish to find what john was in the month of Jan by using Vlookup or
some other formula.

thanks in advance


"T. Valko" wrote:

One way:

=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

Better to use cells to hold the criteria:

F1 = Jan
G1 = John

=SUMPRODUCT(--(A1:A5=F1),--(B1:B5=G1),C1:C5)

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the
desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

Wait a minute, i think I copied the formula wrongly. Just tried it once again
and it seem to be working.
But can you explain this formula to me please. Though I get the answer to my
question, but unable to understand this formula.

Thanks a ton once again.
nsd

"T. Valko" wrote:

One way:

=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

Better to use cells to hold the criteria:

F1 = Jan
G1 = John

=SUMPRODUCT(--(A1:A5=F1),--(B1:B5=G1),C1:C5)

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the
desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

thanks madhu, but I already got the answer from T Valko. Your formula is
giving the result as 4, which is not correct. However thanks a lot. You all
are genius.
nsd

"muddan madhu" wrote:

try this

=INDEX(C1:C5,MATCH("jan",A1:A5,0)*MATCH("john",B1: B5,0),0)



On Sep 23, 1:18 pm, nsd wrote:
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

can you explain this formula to me please.
=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

SUMPRODUCT returns the "sum of products" which is simply arrays of numbers
multiplied together then those results are added up to arrive at the final
result. To make this work we need to convert logical tests to numeric values
that can be multiplied together.

Each of these expressions will return an array of logical values TRUE or
FALSE:

(A1:A5="Jan")
(B1:B5="John")

T = TRUE, F = FALSE

A1 = Jan = T
A2 = Jan = F
A3 = Jan = F
A4 = Jan = F
A5 = Jan = F

B1 = John = T
B2 = John = F
B3 = John = T
B4 = John = F
B5 = John = T

One way to convert those logical values to numbers is to use the double
unary "--". The "--" coerces the TRUE and FALSE to numeric values. TRUE = 1,
FALSE = 0

--(A1 = Jan) = 1
--(A2 = Jan) = 0
--(A3 = Jan) = 0
--(A4 = Jan) = 0
--(A5 = Jan) = 0

--(B1 = John) = 1
--(B2 = John) = 0
--(B3 = John) = 1
--(B4 = John) = 0
--(B5 = John) = 1

Now, we have 3 arrays of numbers that can be multiplied together:

The 1st array is --(A1:A5="Jan")
The 2nd array is --(B1:B5="John")
The 3rd array is C1:C5

1*1*2 = 2
0*0*3 = 0
0*1*4 = 0
0*0*2 = 0
0*1*3 = 0

Now SUMPRODUCT adds up these results of the multiplication to arrive at the
final result:

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

So: Jan and John = 2

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
Wait a minute, i think I copied the formula wrongly. Just tried it once
again
and it seem to be working.
But can you explain this formula to me please. Though I get the answer to
my
question, but unable to understand this formula.

Thanks a ton once again.
nsd

"T. Valko" wrote:

One way:

=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

Better to use cells to hold the criteria:

F1 = Jan
G1 = John

=SUMPRODUCT(--(A1:A5=F1),--(B1:B5=G1),C1:C5)

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the
desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

Valko,
I salute you, you are genius. Great to know about that SumProduct can be
used this way as well. As I always used Sumproduct, but never thought you can
use it this way too.

thanks once again.
nsd

"T. Valko" wrote:

can you explain this formula to me please.
=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

SUMPRODUCT returns the "sum of products" which is simply arrays of numbers
multiplied together then those results are added up to arrive at the final
result. To make this work we need to convert logical tests to numeric values
that can be multiplied together.

Each of these expressions will return an array of logical values TRUE or
FALSE:

(A1:A5="Jan")
(B1:B5="John")

T = TRUE, F = FALSE

A1 = Jan = T
A2 = Jan = F
A3 = Jan = F
A4 = Jan = F
A5 = Jan = F

B1 = John = T
B2 = John = F
B3 = John = T
B4 = John = F
B5 = John = T

One way to convert those logical values to numbers is to use the double
unary "--". The "--" coerces the TRUE and FALSE to numeric values. TRUE = 1,
FALSE = 0

--(A1 = Jan) = 1
--(A2 = Jan) = 0
--(A3 = Jan) = 0
--(A4 = Jan) = 0
--(A5 = Jan) = 0

--(B1 = John) = 1
--(B2 = John) = 0
--(B3 = John) = 1
--(B4 = John) = 0
--(B5 = John) = 1

Now, we have 3 arrays of numbers that can be multiplied together:

The 1st array is --(A1:A5="Jan")
The 2nd array is --(B1:B5="John")
The 3rd array is C1:C5

1*1*2 = 2
0*0*3 = 0
0*1*4 = 0
0*0*2 = 0
0*1*3 = 0

Now SUMPRODUCT adds up these results of the multiplication to arrive at the
final result:

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

So: Jan and John = 2

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
Wait a minute, i think I copied the formula wrongly. Just tried it once
again
and it seem to be working.
But can you explain this formula to me please. Though I get the answer to
my
question, but unable to understand this formula.

Thanks a ton once again.
nsd

"T. Valko" wrote:

One way:

=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

Better to use cells to hold the criteria:

F1 = Jan
G1 = John

=SUMPRODUCT(--(A1:A5=F1),--(B1:B5=G1),C1:C5)

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the
desire
result from column c i.e. 2

Vlookup for multiple values and return to one desired value
 

You're welcome. Thanks for the feedback!

For more on SUMPRODUCT see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
Valko,
I salute you, you are genius. Great to know about that SumProduct can be
used this way as well. As I always used Sumproduct, but never thought you
can
use it this way too.

thanks once again.
nsd

"T. Valko" wrote:

can you explain this formula to me please.
=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

SUMPRODUCT returns the "sum of products" which is simply arrays of
numbers
multiplied together then those results are added up to arrive at the
final
result. To make this work we need to convert logical tests to numeric
values
that can be multiplied together.

Each of these expressions will return an array of logical values TRUE or
FALSE:

(A1:A5="Jan")
(B1:B5="John")

T = TRUE, F = FALSE

A1 = Jan = T
A2 = Jan = F
A3 = Jan = F
A4 = Jan = F
A5 = Jan = F

B1 = John = T
B2 = John = F
B3 = John = T
B4 = John = F
B5 = John = T

One way to convert those logical values to numbers is to use the double
unary "--". The "--" coerces the TRUE and FALSE to numeric values. TRUE =
1,
FALSE = 0

--(A1 = Jan) = 1
--(A2 = Jan) = 0
--(A3 = Jan) = 0
--(A4 = Jan) = 0
--(A5 = Jan) = 0

--(B1 = John) = 1
--(B2 = John) = 0
--(B3 = John) = 1
--(B4 = John) = 0
--(B5 = John) = 1

Now, we have 3 arrays of numbers that can be multiplied together:

The 1st array is --(A1:A5="Jan")
The 2nd array is --(B1:B5="John")
The 3rd array is C1:C5

1*1*2 = 2
0*0*3 = 0
0*1*4 = 0
0*0*2 = 0
0*1*3 = 0

Now SUMPRODUCT adds up these results of the multiplication to arrive at
the
final result:

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

So: Jan and John = 2

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
Wait a minute, i think I copied the formula wrongly. Just tried it once
again
and it seem to be working.
But can you explain this formula to me please. Though I get the answer
to
my
question, but unable to understand this formula.

Thanks a ton once again.
nsd

"T. Valko" wrote:

One way:

=SUMPRODUCT(--(A1:A5="Jan"),--(B1:B5="John"),C1:C5)

Better to use cells to hold the criteria:

F1 = Jan
G1 = John

=SUMPRODUCT(--(A1:A5=F1),--(B1:B5=G1),C1:C5)

--
Biff
Microsoft Excel MVP


"nsd" wrote in message
...
I have some data as under

A B C D
Jan John 2 3
Feb Peter 3 4
Mar John 4 2
Apr Dan 2 3
May John 3 2

What should I do to Vlookup for John for the month of Jan to get the
desire
result from column c i.e. 2