a long formula, cna it be shortened

[pboost1]

i have this formula, but is there anyway it can be shortened and stil
do what the function does?

=IF(MONTH(C1)=MONTH(Sheet1!F2),Sheet1!A2,IF(MONTH(C1)=MONTH(Sheet1!F3),Sheet1!A3,IF(MONTH(C1)=MONTH(Sheet1!F4),Sheet1!A4,IF(MONTH(C1)=MONTH(Sheet1!F5),Sheet1!A5,IF(MONTH(C1)=MONTH(Sheet1!F6),Sheet1!A6,IF(MONTH(C1)=MONTH(Sheet1!F7),Sheet1!A7,0))))))

thank you for any help anybody can give in advance

 

[Bob Umlas]

Array-enter (ctrl+shift+enter):
=INDEX(Sheet1!A2:A7,MATCH(MONTH(C1),MONTH(Sheet1!F2:F7),0))

Bob Umlas
Excel MVP

 

[Soo Cheon Jheong]

Hi,

Use one of these formulas(array formulas):

=OFFSET(Sheet1!A1,MATCH(MONTH(C1),MONTH(Sheet1!F2:F7),0),0)

or

=INDIRECT("Sheet1!A"&MIN(IF(MONTH($C$1)=MONTH(Sheet1!F2:F7),
ROW(Sheet1!A2:A7),"")))


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[RagDyeR]

Can someone please enlighten me as to why this is an array formula when
dates are the criteria, but a regular formula if numbers and/or text are the
arguments?
--

TIA,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Array-enter (ctrl+shift+enter):
=INDEX(Sheet1!A2:A7,MATCH(MONTH(C1),MONTH(Sheet1!F2:F7),0))

Bob Umlas
Excel MVP

 

[Aladin Akyurek]

The MONTH(Sheet1!F2:F7) bit would evaluate to an array which becomes
accessible to MATCH as such only with control+shift+enter.

Can someone please enlighten me as to why this is an array formula when
dates are the criteria, but a regular formula if numbers and/or text are the
arguments?
--

TIA,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Array-enter (ctrl+shift+enter):
=INDEX(Sheet1!A2:A7,MATCH(MONTH(C1),MONTH(Sheet1!F2:F7),0))

Bob Umlas
Excel MVP

[...]

 

[pboost1]

I've tried learning arrays, and indexes and can't get the formula t
work right. Can someone please help