• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert formula from array formula without use ctrl sht enter

R

RAM72

Member
Hi

How to convert this formula without use of crtl shift enter in excel 2007

Code: 
=INDEX('Summary Report '!$I$2:$I$124,MATCH(Workings!G3&Workings!H3&Workings!J3,'Summary Report '!$A$2:$A$124&'Summary Report '!$B$2:$B$124&'Summary Report '!$C$2:$C$124,0))
 
If you want an alternative formula which does not require CTRL+SHIFT+ENTER, provide a sample file.
As an aside, do you not like Array formulas in general, or do you not want to have to use C+S+E? (Not all Array formulas need the C+S+E)
 
Thanks for posting this - I've got to leave here for several hours as I have a golf game, but I'm sure the Ninjas will be waking up in India soon and will have this solved in no time -

I took a cursory glance at it, and don't get what you're Itemizing in Col Y of the Workings ... can you elaborate a little further? More info will help the Ninjas get you a prompt answer as they don't have to do too much forensic work on your current formula

I did my usual of making your data into Tables .... but do they work in Excel 2007? I'm not sure now and rushing ... hope you get a response soon
 
Following formula works with posted data. Check with your original information.
=LOOKUP(2,FIND(G2&H2&J2,'Summary Report '!$A$1:$A$124&'Summary Report '!$B$1:$B$124&'Summary Report '!$C$1:$C$124),'Summary Report '!$I$1:$I$124)
 
Hi RAM72,

Or you can also try SUMIFS with just enter:
=SUMIFS('Summary Report '!$I$1:$I$124,'Summary Report '!$A$1:$A$124,G2,'Summary Report '!$B$1:$B$124,H2,'Summary Report '!$C$1:$C$124,J2)

Regards,
 
You must log in or register to reply here.
Back
Top