Index Match multiple criteria

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,203
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,117
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,050
Messages
5,484,417
Members
407,438
Latest member
DKrakken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top