Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
I hope someone can help!
I have the following data col K col L col Q 40066 $2000.00 80962 40066 $10,000.00 80963 40066 $5.00 80964 40067 $75.00 80966 40067 $20.00 80900 40068 $2200.00 80901 I need the value in col L if col k is 40066 and col q is 80964. All values are always within one row, meaning that if 40066 and 80964 are found, the $$ will be in that row also. Does this make sense?? -- Many Thanks, Paula |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
If the Col_L values will always be numeric
AND there will only be ONE Col_L value for any combination of Col_K and Col_Q, then try this: A1: 40066 A2: 80964 B1: =SUMPRODUCT((K1:K10=A1)*(Q1:Q10=A2)*L1:L10) In this case the formula returns 5. Change range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Billing Goddess" wrote: I hope someone can help! I have the following data col K col L col Q 40066 $2000.00 80962 40066 $10,000.00 80963 40066 $5.00 80964 40067 $75.00 80966 40067 $20.00 80900 40068 $2200.00 80901 I need the value in col L if col k is 40066 and col q is 80964. All values are always within one row, meaning that if 40066 and 80964 are found, the $$ will be in that row also. Does this make sense?? -- Many Thanks, Paula |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
=SOMMEPROD((K2:K7=40066)*(Q2:Q7=80964);,L2:L7)
HTH -- AP "Billing Goddess" a écrit dans le message de ... I hope someone can help! I have the following data col K col L col Q 40066 $2000.00 80962 40066 $10,000.00 80963 40066 $5.00 80964 40067 $75.00 80966 40067 $20.00 80900 40068 $2200.00 80901 I need the value in col L if col k is 40066 and col q is 80964. All values are always within one row, meaning that if 40066 and 80964 are found, the $$ will be in that row also. Does this make sense?? -- Many Thanks, Paula |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
=INDEX(L1:L100,MATCH(40066&80964,K1:K100&Q1:Q100,0 ))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Billing Goddess" wrote in message ... I hope someone can help! I have the following data col K col L col Q 40066 $2000.00 80962 40066 $10,000.00 80963 40066 $5.00 80964 40067 $75.00 80966 40067 $20.00 80900 40068 $2200.00 80901 I need the value in col L if col k is 40066 and col q is 80964. All values are always within one row, meaning that if 40066 and 80964 are found, the $$ will be in that row also. Does this make sense?? -- Many Thanks, Paula |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
Thanks, but I had to use vlookup in order for this to work properly.. but I
was able to use the index formula for another application! Thanks -- Many Thanks, Paula "Bob Phillips" wrote: =INDEX(L1:L100,MATCH(40066&80964,K1:K100&Q1:Q100,0 )) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Billing Goddess" wrote in message ... I hope someone can help! I have the following data col K col L col Q 40066 $2000.00 80962 40066 $10,000.00 80963 40066 $5.00 80964 40067 $75.00 80966 40067 $20.00 80900 40068 $2200.00 80901 I need the value in col L if col k is 40066 and col q is 80964. All values are always within one row, meaning that if 40066 and 80964 are found, the $$ will be in that row also. Does this make sense?? -- Many Thanks, Paula |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
Ardus Petus wrote...
=SOMMEPROD((K2:K7=40066)*(Q2:Q7=80964);,L2:L7) Unhelpful to use non-English function names in English language newsgroups without mentioning that you don't know the English function name. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
Billing Goddess wrote...
Thanks, but I had to use vlookup in order for this to work properly.. but I was able to use the index formula for another application! Thanks .... You couldn't have used VLOOKUP to solve the question you posed as you posed. Did you insert a column to the left of your column L that contained formulas that concatenated the values in your original columns K and Q? If not, you may think you've solved this, but it's more likely your formula happenstantially returns the correct value for the specific data you're using. As the saying goes, a stopped clock shows the correct time twice a day. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple selections
Bob Phillips wrote...
=INDEX(L1:L100,MATCH(40066&80964,K1:K100&Q1:Q100, 0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. .... An alternative that doesn't depend on fixed width fields in columns K and Q, =INDEX(L1:L100,MATCH(1,(K1:K100=40066)*(Q1:Q100=80 964),0)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |