Results 1 to 8 of 8

Index Match matching two criteria

Threaded View

  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:
        Dim rng1 As Range
        Dim rng2 As Range
        Dim rng3 As Range
    
        Set rng1 = Sheets("Source").Range("$A$1:$A$300")
        Set rng2 = Sheets("Source").Range("$B$1:$B$300")
        Set rng3 = Sheets("Source").Range("$C$1:$C$300")
        
           
        For i = 2 To 20     
            
            Cells(i, 3) = Evaluate("Index(rng3" & "Match(1,(rng1=(Cells(i, 1)*(rng2=(Cells(i,2),0))"    
              
        Next
    Help!
    Last edited by rish3005; 05-09-2017 at 12:41 PM.

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