# 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
###### Who is Participating?

Commented:
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

ConsultantCommented:
Hi,

I cannot see the string CSE anywhere in there.

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

Thanks,

Alan.
0

ConsultantCommented:
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

Author 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

Commented:
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.

2

Author 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.