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

1. ## 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  Reply With Quote

2. ## 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```  Reply With Quote

## User Tag List

#### Tags for this Thread

array, column, formula, index, match #### Posting Permissions

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