Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
a b c d e
f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
Try these array formulas** :
For a specific year: =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=yn,F1:F7),1),F1: F7,0)) For a specific month: =INDEX(E1:E7,MATCH(LARGE(IF(C1:C7=mn,F1:F7),1),F1: F7,0)) For a specific month and year: =INDEX(E1:E7,MATCH(LARGE(IF((B1:B7=yn)*(C1:C7=mn), F1:F7),1),F1:F7,0)) Whe yn = year number, mn = month number Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "need a help" wrote in message ... a b c d e f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
yes thank you Proff. T. Valko i have added you in my hotmail so kindly accep
me this is one the another one can i refer for Yn or Mn to specific cell for exmple H1=2008 H2=2007 and how i will make the formulas to do that many thanks for you "T. Valko" wrote: Try these array formulas** : For a specific year: =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=yn,F1:F7),1),F1: F7,0)) For a specific month: =INDEX(E1:E7,MATCH(LARGE(IF(C1:C7=mn,F1:F7),1),F1: F7,0)) For a specific month and year: =INDEX(E1:E7,MATCH(LARGE(IF((B1:B7=yn)*(C1:C7=mn), F1:F7),1),F1:F7,0)) Whe yn = year number, mn = month number Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "need a help" wrote in message ... a b c d e f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
Just replace yn/mn with the cell reference:
For year number 2008... H1 = 2008 =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=H1,F1:F7),1),F1: F7,0)) Don't forget, array enter! -- Biff Microsoft Excel MVP "need a help" wrote in message ... yes thank you Proff. T. Valko i have added you in my hotmail so kindly accep me this is one the another one can i refer for Yn or Mn to specific cell for exmple H1=2008 H2=2007 and how i will make the formulas to do that many thanks for you "T. Valko" wrote: Try these array formulas** : For a specific year: =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=yn,F1:F7),1),F1: F7,0)) For a specific month: =INDEX(E1:E7,MATCH(LARGE(IF(C1:C7=mn,F1:F7),1),F1: F7,0)) For a specific month and year: =INDEX(E1:E7,MATCH(LARGE(IF((B1:B7=yn)*(C1:C7=mn), F1:F7),1),F1:F7,0)) Whe yn = year number, mn = month number Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "need a help" wrote in message ... a b c d e f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
yes i have don't before i sent to you but it give #VALUE! another thing the
other ques you answered ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" does not work so would you make another way for both ques, "T. Valko" wrote: Just replace yn/mn with the cell reference: For year number 2008... H1 = 2008 =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=H1,F1:F7),1),F1: F7,0)) Don't forget, array enter! -- Biff Microsoft Excel MVP "need a help" wrote in message ... yes thank you Proff. T. Valko i have added you in my hotmail so kindly accep me this is one the another one can i refer for Yn or Mn to specific cell for exmple H1=2008 H2=2007 and how i will make the formulas to do that many thanks for you "T. Valko" wrote: Try these array formulas** : For a specific year: =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=yn,F1:F7),1),F1: F7,0)) For a specific month: =INDEX(E1:E7,MATCH(LARGE(IF(C1:C7=mn,F1:F7),1),F1: F7,0)) For a specific month and year: =INDEX(E1:E7,MATCH(LARGE(IF((B1:B7=yn)*(C1:C7=mn), F1:F7),1),F1:F7,0)) Whe yn = year number, mn = month number Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "need a help" wrote in message ... a b c d e f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
yes it's done i forgot the array enter
but you have to make anoter way for the second ques ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" it does not work so give me your email that you use and i will be thakfull "T. Valko" wrote: Just replace yn/mn with the cell reference: For year number 2008... H1 = 2008 =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=H1,F1:F7),1),F1: F7,0)) Don't forget, array enter! -- Biff Microsoft Excel MVP "need a help" wrote in message ... yes thank you Proff. T. Valko i have added you in my hotmail so kindly accep me this is one the another one can i refer for Yn or Mn to specific cell for exmple H1=2008 H2=2007 and how i will make the formulas to do that many thanks for you "T. Valko" wrote: Try these array formulas** : For a specific year: =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=yn,F1:F7),1),F1: F7,0)) For a specific month: =INDEX(E1:E7,MATCH(LARGE(IF(C1:C7=mn,F1:F7),1),F1: F7,0)) For a specific month and year: =INDEX(E1:E7,MATCH(LARGE(IF((B1:B7=yn)*(C1:C7=mn), F1:F7),1),F1:F7,0)) Whe yn = year number, mn = month number Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "need a help" wrote in message ... a b c d e f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
index-match and another condition
for the second ques
="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" it does not work Ooops! I left out the sheet name. See the other post. -- Biff Microsoft Excel MVP "need a help" wrote in message ... yes it's done i forgot the array enter but you have to make anoter way for the second ques ="='C:\Documents and Settings\VENUS\Desktop\["&A1&"]'!$A$1" it does not work so give me your email that you use and i will be thakfull "T. Valko" wrote: Just replace yn/mn with the cell reference: For year number 2008... H1 = 2008 =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=H1,F1:F7),1),F1: F7,0)) Don't forget, array enter! -- Biff Microsoft Excel MVP "need a help" wrote in message ... yes thank you Proff. T. Valko i have added you in my hotmail so kindly accep me this is one the another one can i refer for Yn or Mn to specific cell for exmple H1=2008 H2=2007 and how i will make the formulas to do that many thanks for you "T. Valko" wrote: Try these array formulas** : For a specific year: =INDEX(E1:E7,MATCH(LARGE(IF(B1:B7=yn,F1:F7),1),F1: F7,0)) For a specific month: =INDEX(E1:E7,MATCH(LARGE(IF(C1:C7=mn,F1:F7),1),F1: F7,0)) For a specific month and year: =INDEX(E1:E7,MATCH(LARGE(IF((B1:B7=yn)*(C1:C7=mn), F1:F7),1),F1:F7,0)) Whe yn = year number, mn = month number Format as DATE ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "need a help" wrote in message ... a b c d e f e 1 2007 5 18 18/5/2007 35 a 2 2007 6 21 21/6/2007 38 b 3 2007 6 22 22/6/2007 34.75 c 4 2008 6 23 23/6/2008 35 d 5 2008 5 24 24/5/2008 33 e 6 2008 6 25 25/6/2008 36 f 7 2008 6 28 28/6/2008 36.5 h i am using this one =INDEX($E$1:$E$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 21/6/2007 =INDEX($F$1:$F$7,MATCH(LARGE($F$1:$F$7,1),$F$1:$F$ 7,FALSE)) the result will be = 38 but when i need to know the largest in 2007 i mean by column (B) or by month by column (C) or by both (B)&(C) year and month i have tried many way but i didn't get it , so any nice programmer tell me how i can do that and many thanks for him in advace |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
how do I delete all rows that match a condition? | Excel Worksheet Functions |