# index-match and another condition

N

#### need a help

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

T

#### T. Valko

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))

Where: 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)

N

#### need a help

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

#### T. Valko

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!

N

#### need a help

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,

N

#### need a help

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

#### T. Valko

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 said:
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 said:
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!