Index Match multiple criteria
Results 1 to 2 of 2

Thread: Index Match multiple criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,043
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Index Match multiple criteria

    I am trying to create an index match formula based on multiple criteria, as per this article:

    Code:
    https://exceljet.net/formula/index-and-match-with-multiple-criteria
    In particular, I would like to use the NON-ARRAY formula, as stated in the article, ie:

    Code:
    =INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
    
    This is my code but it errors on the last line:

    Code:
        Dim rng1 As Range
        
        With Sheet1
        
            Set rng1 = .Range(.Cells(5, 5), .Cells(11, 5))
    
        End With
    
        Dim rng2 As Range
        
        With Sheet1
        
            Set rng2 = .Range(.Cells(5, 2), .Cells(11, 2))
    
        End With
        
        Dim rng3 As Range
        
        With Sheet1
        
            Set rng3 = .Range(.Cells(5, 3), .Cells(11, 3))
    
    
        End With
        
        Dim rng4 As Range
        
        With Sheet1
        
            Set rng4 = .Range(.Cells(5, 4), .Cells(11, 4))
        
        End With
     
        Dim abc
        
        abc = Application.Index(rng1, Application.Match(1, Application.Index(("T-shirt" = rng2) * ("Large" = rng3) * ("Red" = rng4), 0, 1), 0))
    
    


    Can someone please tell me what is wrong?

    Thanks


    Last edited by tiredofit; Aug 13th, 2019 at 11:40 AM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Index Match multiple criteria

    Try this way

    Code:
    Sub test()
      Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, abc As Variant
      Set rng1 = Range(Cells(5, 5), Cells(11, 5))
      Set rng2 = Range(Cells(5, 2), Cells(11, 2))
      Set rng3 = Range(Cells(5, 3), Cells(11, 3))
      Set rng4 = Range(Cells(5, 4), Cells(11, 4))
      abc = Evaluate("Index(" & rng1.Address & ",Match(1,(""T-shirt""=" & rng2.Address & ")*" & _
        "(""Large""=" & rng3.Address & ")*(""Red""=" & rng4.Address & "),0))")
    End Sub
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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