# 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Mechanical EngineerCommented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
Mechanical EngineerCommented:
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.