How to remove the requirement of CSE in this formula

How do I change this formula, so that it does not require array-entry (the keystroke of CSE).

=LOOKUP(2,1/(MIN(IF((MONTH(Sheet1!$C$3:$C$1002)=Sheet1!$G3)*Sheet1!$C$3:$C$1002>0,(MONTH(Sheet1!$C$3:$C$1002)=Sheet1!$G3)*Sheet1!$C$3:$C$1002))=Sheet1!$C$3:$C$1002),Sheet1!$D$3:$D$1002)
LVL 6
FloraAsked:
Who is Participating?
 
byundtCommented:
I tried the following non-CSE formula, and its results agreed with your CSE formula using my test data:
=LOOKUP(2,1/(AGGREGATE(15,6,Sheet1!$C$3:$C$1002/((MONTH(Sheet1!$C$3:$C$1002)=Sheet1!$G3)*(Sheet1!$C$3:$C$1002>0)),1)=Sheet1!$C$3:$C$1002),Sheet1!$D$3:$D$1002)

AGGREGATE requires Excel 2010 or later.
1
 
AlanConsultantCommented:
Hi,

I cannot see the string CSE anywhere in there.

Maybe copy / paste again from a cell that does contain that?

Thanks,

Alan.
0
 
AlanConsultantCommented:
Are you trying to ask how to convert it from an array formula?

If so, what are you actually trying to achieve?  What is the problem with it?

Alan.
1
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
FloraAuthor Commented:
byundt,  you are truly a genius!  I almost believed that it is impossible but you really surprised me.

Thanks a Million.  You did it even without the actual sample workbook. thanks again.
0
 
byundtCommented:
Flora,
Thanks for the kind words!

It took me a while to figure out what the original formula was trying to do. You might have gotten an answer sooner (before I got involved) had you offered an explanation.

Brad
2
 
FloraAuthor Commented:
Thanks byundt.

Wish you a very good weekend!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.