| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup Return Multiple Values | Excel Discussion (Misc queries) | |||
| How do I return Multiple values using VLookup? | Excel Worksheet Functions | |||
| IRR desired return | Excel Worksheet Functions | |||
| vlookup one value and return multiple values | Excel Discussion (Misc queries) | |||
| Returning the desired value if multiple values exist???? | Excel Worksheet Functions | |||
Linear Mode
