+ Reply to Thread
Results 1 to 8 of 8

Index Match matching two criteria

  1. #1
    Registered User
    Join Date
    05-09-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    6

    Exclamation Index Match matching two criteria

    Hi all,

    I have a workbook with two tabs (Source and report)
    Source contains 3 columns: Country(col-A), Product(col-B) & Business unit(col-C)

    In the report I'm trying to use a macro to create an index match to map the Country and Product to return a Business Unit.
    (Country = Col A, Product = Col B, Business Unit (to be mapped in Col C)

    My aim is for the loop to run in the report tab from Cells(2,3) to Cells(20,3).

    I've seen examples of the "evaluate" syntax used, but haven't been able to make it work.

    This is what I have so far:
    Please Login or Register  to view this content.
    Help!
    Last edited by rish3005; 05-09-2017 at 12:41 PM.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Index Match matching two criteria

    You need to evaluate formula as string for array formula I believe.

    Try something like...
    Please Login or Register  to view this content.
    However, looks like Cells(i, 3) will cause circular reference?

    Edit2:
    Ah, it's been corrected. I've corrected my code as well.
    Last edited by CK76; 05-09-2017 at 12:46 PM.

  3. #3
    Registered User
    Join Date
    05-09-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    6

    Re: Index Match matching two criteria

    Thanks for your reply but I'm still getting a "#VALUE" error...

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Index Match matching two criteria

    Can you upload sample file? I suspect you may need to concatenate address with sheet name etc. But hard to test without data.

  5. #5
    Registered User
    Join Date
    05-09-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    6

    Re: Index Match matching two criteria

    I believe that's attached now!
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Index Match matching two criteria

    Here you go. Since you will be using Evaluate on formula string, you don't need to set ranges. Just use it in string construction instead (or set it as string variable instead of range).

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-09-2017
    Location
    london
    MS-Off Ver
    2010
    Posts
    6

    Re: Index Match matching two criteria

    Works like a treat! thanks so much!

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Index Match matching two criteria

    You are welcome

+ 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. Index partial Matching with at least 3 characters matching per criteria
    By T86157 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2017, 03:15 PM
  2. Matching 3 criteria using index and match formula
    By fordieuk in forum Excel General
    Replies: 6
    Last Post: 10-21-2014, 10:08 AM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  5. [SOLVED] Need help using Index and Match for table lookup with 2 criteria matching
    By lorne17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2012, 09:26 AM
  6. [SOLVED] Index/Match Not Matching Correctly on Multiple Criteria Across Sheets
    By cbauer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-02-2012, 04:19 PM
  7. Replies: 2
    Last Post: 03-31-2009, 11:04 AM

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