VBA: How to Use INDEX MATCH with Multiple Criteria


You can use the following basic syntax to perform an INDEX MATCH with multiple criteria in VBA:

Sub IndexMatchMultiple()
    Range("F3").Value = WorksheetFunction.Index(Range("C2:C10"), _
    WorksheetFunction.Match(Range("F1"), Range("A2:A10"), 0) + _
    WorksheetFunction.Match(Range("F2"), Range("B2:B10"), 0) - 1)
End Sub

This particular example looks up the value in cell F1 within the range A2:A10 and the value in cell F2 within the range B2:B10 and returns the corresponding value in the range C2:C10 to cell F3.

The following example shows how to use this syntax in practice.

Example: Perform INDEX MATCH with Multiple Criteria Using VBA

Suppose we have the following dataset in Excel that contains information about basketball players:

Suppose we would like to look up the player that matches the team name in cell F1 and the position in cell F2 and return the name in cell F3.

We can create the following macro to do so:

Sub IndexMatchMultiple()
    Range("F3").Value = WorksheetFunction.Index(Range("C2:C10"), _
    WorksheetFunction.Match(Range("F1"), Range("A2:A10"), 0) + _
    WorksheetFunction.Match(Range("F2"), Range("B2:B10"), 0) - 1)
End Sub

When we run this macro, we receive the following output:

The macro looks up “Spurs” in the Team column and “Forward” in the Position column and correctly returns the name “Eric” in cell F3.

If we change the values in cells F1 and F2 and run the macro again, it will be able to find the player name based on the new values.

For example, suppose we change the team name to “Mavs” and position to “Center” and run the macro again:

The macro looks up “Mavs” in the Team column and “Center” in the Position column and correctly returns the name “Chad” in cell F3.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Use INDEX MATCH
VBA: How to Check if String Contains Another String
VBA: How to Count Number of Rows in Range

Featured Posts

3 Replies to “VBA: How to Use INDEX MATCH with Multiple Criteria”

  1. Hi Zach,

    Thank you for posting this. It is very helpful. How can this be modified to
    1. Perform this matching using data from 2 tabs in the same excel document
    2. Keep running for the entire data set even if some cells that do not have a match?

    Let me try to make sense of what I am asking:

    Using your example above: lets say that columns A/B/C are on another tab but the information in cells 4, 5, 9 of tab 1 don’t exist in this tab (so there is only A2:A6, B2:B6 and C2:C6). Call this ‘tab 2’. In tab 1, there is no column C.
    In both tabs, I have successfully combined the information in columns A and B onto column D using =A2&” and “&B2 in D2 then drag down to D10 (D6 in tab 2). For example, D2 would show ‘Mavs and Guard’.

    The match that I want to run is one where I am prompted to select my range (D2:D10 in tab 1) to compare to my range (D2:D6 in tab 2), my range for lookup is C2:C6 in tab 2 and my match results will be deposited in the appropriate cells in E2:E10 of tab 1 (I say appropriate because in my scenario, cells E4, E5 and E9 of tab 1 will be blank). Cell E2 in tab 1 will have ‘Andy’, cell E3 will have ‘Bob’, cell E4 will be blank, etc etc all the way to cell E10 which will have ‘Isaac’.

    How can I update your original macro to run my scenario?

  2. Hi Moderator,

    I don’t see my comment from a few days ago. Is there something that I need to change for my comment (question) to get to Zach? Please, kindly let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *