Index Match with multiple Criteria

[Kellogg]

Dear Great Minds of Excel,

I have the following information:

Column A = Vehicle Numbers
Column D = Dates of Service
Column E = Type of Service

A D E
A111 01/22 Oil Change
A112 01/30 Brakes
A113 01/15 Wash
A111 01/18 Tires
A123 02/05 Oil Change


The following code will return the date 01/22.

Dim MYOil As Double
MYOil = Application.WorksheetFunction.Index(Worksheets("Activity_Log").Range("D:D") _
, Application.WorksheetFunction. _
  Match("Oil Change" _
, Worksheets("Activity_Log").Range("E:E") _
, 0), 0)
MsgBox MYOil

What I would like to do is add an additional match criteria to include the Vehicle Number. When I try this as written below I only receive errors.

Dim MYOil As Double
MYOil = Application.WorksheetFunction.Index(Worksheets("Activity_Log").Range("D:D") _
, Application.WorksheetFunction. _
  Match("Oil Change"[COLOR="#FF0000"] & "A123"[/COLOR] _
, Worksheets("Activity_Log").Range("E:E")[COLOR="#FF0000"] & Worksheets("Activity_Log").Range("A:A")[/COLOR] _
, 0), 0)
MsgBox MYOil

Any suggestions?

As always - Thanks


/s/


Craig

 

[AlphaFrog]

Try this...

[color=darkblue]Dim[/color] MYOil [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
MYOil = Evaluate("INDEX(Activity_Log!D1:D10," & _
        "MATCH(1,(Activity_Log!E1:E10 = ""Oil Change"")*(Activity_Log!A1:A10=""A123""),0))")
[color=darkblue]If[/color] [color=darkblue]Not[/color] IsError(MYOil) [color=darkblue]Then[/color]
    MsgBox MYOil
[color=darkblue]Else[/color]
    MsgBox "No match found"
[color=darkblue]End[/color] [color=darkblue]If[/color]

 

[Kellogg]

AlphaFrog,

Thanks for the help. This works great!