Hi Avinash, The problem is that you are use massive ranges -- entire columns, which is many millions of cells.
I see you already have a definied name (rptList) that is a dynamic range for the data on your "txt List" sheet. You could just using that named range in your formulas would pretty much resolve your speed issues. However, you may want to use Excel 2007/2010's Table feature and make your data tables official Excel Tables. To do so, highlight each table of data, including the headers, and choose INSERT|Tables>Table. Then go to FORMULAS|Defined Names>Name Manager and rename the tables from Table1, Table2, etc. to suit (perhaps tbl_txtList, tbl_UserNames, etc.) Tables are automatically dynamic ranges, and Excel can handle them faster and more efficiently than other dynamic ranges, and it can sort and filter them more efficiently. Tables also give you structured references, which allow you to refer to tables, columns, headers, total rows, etc. by name. See https://office.microsoft.com/en-us/excel-help/using-structured-references-wi th-excel-tables-HA010155686.aspx for all the details.. but here are some examples for your file: in Setts!E8: =vbaVlookup(D2,tbl_txtList,3,"A") in Setts!F8: =VLOOKUP(E8,tbl_txtList[[Report Key]:[Report]],3,FALSE) in Setts!A2: =IFERROR(MATCH([@UserName],[UserName],0),"") in 'txt List'!A2: =MATCH([@Responsibility],tbl_UserNames[UserName],0) in 'txt List'!C2: =[@UserMap]&"--"&SUMPRODUCT(--([Responsibility]:[@Responsibility]=[@Responsi bility])) or even more efficient, for 'txt List'!C2: =[@UserMap]&"--"&COUNTIF([Responsibility]:[@Responsibility],[@Responsibility ]) in 'txt List'!E2: =MATCH([@Report],[Report],0) Note that Excel will write most structured references for you if you just select cells as normal while entering formulas. Other improvements are possible. To get the full advantage of the maintainability of named references in your tables, it's better to use the INDEX/MATCH combination than VLOOKUP so you don't have to refer to columns by number anywhere. It is possible to calculate the relative column number with COLUMNS(), but you may as well just use index/match. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Avinash Sent: Thursday, March 22, 2012 9:57 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Arry Formula help Dear Experts, please open attached excel sheet and go to the Setts sub sheet for better understanding. basically i want to perform multi value lookup for the specific user. and to get this thing done i tried to Google it. now i got the VBA code for the multivalue lookup but it is taking too much time to give me the results. can anyone please help me with this and tell me how it works ( there is a VBA code in the attached file) please help me and thanks Don and Noorain for all your support. and those formulas and codes which you have given me are very very very very usefull in my work thnx a ton guys. Regards, Avinash -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
