+ Reply to Thread
Results 1 to 16 of 16

VBA function to match multiple criteria faster than vlookup or index match

  1. #1
    Registered User
    Join Date
    12-03-2019
    Location
    Berlin
    MS-Off Ver
    2016
    Posts
    23

    VBA function to match multiple criteria faster than vlookup or index match

    Hi Everyone!

    Im working on a project where by I need to be able to match data from two different files and transfer the prices and dates from "Data" to "Data match".

    The data from "Data" does not always have all columns, so I would like for it to loop through and if there is no data to match on what ever other data is available.

    Does any body have any idea of this would be possible and if so how? I have attached and example file for reference. (Yellow columns containing the data to be matched)

    Looking forward to hearing from you!

    Any help would be greatly appreciated!|
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-03-2019
    Location
    Berlin
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Important note to add is that im a looking for this solution as Index match is generally very slow, some times I dealing with upto 1000 rows

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA function to match multiple criteria faster than vlookup or index match

    You need to provide a wider range of examples with before and after results.

    Index/Match shouldn't be that slow over 1000 rows unless you are using full column references.

    What is the formula that you are using that is slow?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    12-03-2019
    Location
    Berlin
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Hi TMS,

    I have updated the file to demonstrate more examples and the Index, match formula with 6 different criteria I use.


    You will probably be able to tell from the file what I mean by slowing down excel.

    is there a way to instantly perform the same match on VBA? With a looping function to ignore a criteria if the cell if empty?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Looking forward to your reply
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA function to match multiple criteria faster than vlookup or index match

    TMS gave you the answer

    Try:

    =INDEX(G1:G2000,MATCH(1,(J8=A1:A2000)*(K8=B1:B2000)*(L8=C:C)*(M8=D1:D2000)*(N8=E1:E2000)*(O8=F1:F2000),0))
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  6. #6
    Registered User
    Join Date
    12-03-2019
    Location
    Berlin
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Thanks, this speeds up slightly but the fear for me is with larger data sets my computer freezes a lot and at time excel can crash.

    Is there a way of getting the same results via VBA?

    Also as the data sets may be different say for example only country and postcode or country and city name?

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: VBA function to match multiple criteria faster than vlookup or index match

    You're likely to be severely disappointed. It's not like MSFT has engineered MATCH to be slow. Indeed, they've likely implemented it as fast as they can.

    It's far more likely that what you're trying to do is inherently slow. VBA is unlikely to make it quicker.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Yep, that would do it. Matrix multiplication of 6 columns of over a million rows x however many copies of that formula. Mehmetcik has given you a better alternative that should be much faster, though he may have missed column C?

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA function to match multiple criteria faster than vlookup or index match

    You might be better having a helper column with the MATCH part in it and use that in all the INDEX formulae.

  10. #10
    Registered User
    Join Date
    12-03-2019
    Location
    Berlin
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Thanks for all the advice! What would be the best way to remove a criteria should that cell be empty?

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Thanks TMS

    TMS gave you the answer

    Try:

    =INDEX(G1:G2000,MATCH(1,(J8=A1:A2000)*(K8=B1:B2000)*(L8=C1:C2000)*(M8=D1:D2000)*(N8=E1:E2000)*(O8=F1:F2000),0))

  12. #12
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: VBA function to match multiple criteria faster than vlookup or index match

    If entire column references are the problem, and the OP is willing to use VBA, then best to use event handlers to keep track of UsedRange in every worksheet.

    ADDED: this would go in the ThisWorkbook class module.

    Please Login or Register  to view this content.
    This would make and update _URLC and _URLR worksheet-level names in each worksheet. Those could be used in formulas like

    =MATCH(1,(X99=A2:INDEX(A:A,_URLR))*(Y999=B2:INDEX(B:B,_URLR)),0)
    Last edited by hrlngrv; 11-08-2020 at 07:44 PM. Reason: addendum

  13. #13
    Registered User
    Join Date
    12-03-2019
    Location
    Berlin
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Hi hrlngrv,

    Thanks for this, I am definitely more interested in using a VBA version. Im a little confused as to how to apply. I would greatly appreciate if you could you possibly apply it to the sample sheet I provided?

    Thanks

  14. #14
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: VBA function to match multiple criteria faster than vlookup or index match

    I showed how _URLC and _URLR could be used in formulas.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The range A2:INDEX(A:A,_URLR) would change as UsedRange expanded (or contracted). For your sample formula,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As a complete tangent, I believe the following is what it'd take to implement general multiple column vertical lookups using short-circuit boolean evaluation. This MIGHT be faster than brute force formulas using no VBA. As with most generalized functions, the bulk of the code is validity and conformity checking. There's also Select Case for each comparison operator as well as numeric or string comparisons. Personally, I hate lexical analysis coding, but every once in a while I need some practice.

    Please Login or Register  to view this content.
    For your example, this could be used in formulas like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or combined with _URLR,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Full VBA, no formula, if you like.
    Remove all formula then run.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: VBA function to match multiple criteria faster than vlookup or index match

    Try this. This is faster
    IN P6 and copied across
    Please Login or Register  to view this content.
    IF you are ready for HELPER column it will be more faster.
    I is the helper column
    In I6 and copied down
    Please Login or Register  to view this content.
    In R6 then copied across.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-09-2020 at 09:24 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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 / index+match / with multiple criteria
    By stopo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2020, 02:13 PM
  2. Replies: 7
    Last Post: 10-03-2019, 11:23 AM
  3. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  4. Multiple criteria vlookup with INDEX and MATCH
    By Ljung in forum Excel General
    Replies: 5
    Last Post: 05-26-2016, 04:45 AM
  5. Faster option than VLOOKUP? INDEX/MATCH is taking twice as long...
    By mrbusto71 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-12-2015, 09:44 PM
  6. [SOLVED] Which method is faster, VLOOKUP or MATCH & INDEX?
    By Jimmyjazz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-20-2013, 04:35 PM
  7. [SOLVED] Index, match, vlookup with multiple criteria
    By saniamarco in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2012, 07:16 PM

Tags for this Thread

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