Index Match multiple criteria

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,208
I am trying to create an index match formula based on multiple criteria, as per this article:

Rich (BB 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:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
[/FONT]
This is my code but it errors on the last line:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim rng1 As Range
    
    With Sheet1
    
        Set rng1 = .Range(.Cells(5, 5), .Cells(11, 5))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    Dim rng2 As Range
    
    With Sheet1
    
        Set rng2 = .Range(.Cells(5, 2), .Cells(11, 2))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    End With
    
    Dim rng3 As Range
    
    With Sheet1
    
        Set rng3 = .Range(.Cells(5, 3), .Cells(11, 3))

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    End With
    
    Dim rng4 As Range
    
    With Sheet1
    
        Set rng4 = .Range(.Cells(5, 4), .Cells(11, 4))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    
    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


[/FONT]
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,208
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,674
Messages
5,488,210
Members
407,633
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top