Vlookup for multiple values and return to one desired value

[nsd]

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

 

[T. Valko]

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)

 

[muddan madhu]

try this

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

 

[nsd]

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

 

[nsd]

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

 

[nsd]

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

 

[T. Valko]

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

 

[nsd]

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]

You're welcome. Thanks for the feedback!

For more on SUMPRODUCT see this:

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