+ Reply to Thread
Results 1 to 5 of 5

UDF Vlookup to look for multiple match and return it in single cell - Speed is pathetic

  1. #1
    Registered User
    Join Date
    05-21-2014
    Posts
    2

    Question UDF Vlookup to look for multiple match and return it in single cell - Speed is pathetic

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 09-04-2014 at 04:05 PM. Reason: Added Code Tags

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: UDF Vlookup to look for multiple match and return it in single cell - Speed is patheti

    Do you have a specific question?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    05-21-2014
    Posts
    2

    Re: UDF Vlookup to look for multiple match and return it in single cell - Speed is patheti

    Thanks for quick response - Is there a way we can make it fast ? I am trying to lookup for Tax Id , range (Tax Entity Code : OPIE) and return the OPIE values to col Formula. Please note that TAX_ID has multiple matches. can we avoid using UDF ?Many thanks

    TAX_ID Formula Tax Entity OPIE
    100 #NAME? 100 85005
    101 #NAME? 101 81317
    102 #NAME? 103 79601
    103 #NAME? 103 82151
    104 #NAME? 104 79701
    105 #NAME? 104 82152
    106 #NAME? 111 95104
    107 #NAME? 112 82201
    108 #NAME? 112 82202
    109 #NAME? 112 86101
    110 #NAME? 113 79602
    111 #NAME? 113 82203
    112 #NAME? 114 79603
    113 #NAME? 114 95101
    114 #NAME? 114 95198
    115 #NAME? 114 79693
    116 #NAME? 117 79602
    117 #NAME? 117 82201
    118 #NAME? 120 79611
    119 #NAME? 120 82312
    120 #NAME? 121 79606
    121 #NAME? 121 82311
    122 #NAME? 121 82313
    123 #NAME? 129 82302
    Last edited by vineet2k; 09-04-2014 at 01:05 PM. Reason: additional info

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF Vlookup to look for multiple match and return it in single cell - Speed is patheti

    I think that, in order to speed it up, the best thing to do would be to rethink the algorithm. Currently, it appears that the algorithm looks at every entry in lookuprange and collects those that match. Naturally, a "linear" search like this is one of the slowest searches, though sometimes it is necessary.

    Another thing I note is that it looks like it will search every single cell across all columns, rather than search within a single column. This further multiplies the number of "tests" the routine is performing.

    The main way I see to speed this up is to think it through so you can reduce the number of operations. My first suggestion would be to require the user to sort the lookuprange based on the search column. This will do two things. It will allow you to use a much faster binary search algorithm and it will put all of your results together in a block within the lookuprange. With the data sorted, you only need to find the first and last entry containing lookupval, then your result will come from the block of cells in between. This approach will require your user to provide more input to the routine, but it will reduce the number of operations the UDF needs to perform -- significantly speeding up the execution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: UDF Vlookup to look for multiple match and return it in single cell - Speed is patheti

    As far as avoiding the UDF altogether, have you explored using filters for this? http://office.microsoft.com/en-us/ex...616.aspx?CTT=1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP to Search Cell with Multiple Entries and Return a Single Value
    By TheRob in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-07-2014, 08:54 AM
  2. Match single criteria and return multiple rows in excel
    By taernster in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2014, 05:53 PM
  3. [SOLVED] How to return multiple values that match a single search criteria?
    By JSallen in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:49 AM
  4. [SOLVED] VLOOKUP to search multiple columns and return value from single column
    By BBExcelusr in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 08-01-2012, 01:12 PM
  5. Replies: 3
    Last Post: 08-17-2010, 02:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1