Excel VBA to Match Value in Range: 3 Methods

Method 1 – Match a Value in Range with VBA Match Function in Excel

STEPS:

• Go to the Developer tab from the ribbon.
• Click on Visual Basic to open the Visual Basic Editor. Alternatively, press Alt + 11 to open the Visual Basic Editor.

• Another way to open Visual Basic Editor is just to right-click on the worksheet and click on View Code.

• Insert the code there.

VBA Code:

Sub example1_match()
Range("G5").Value = WorksheetFunction.Match(Range("F5").Value, Range("D5:D10"), 0)
End Sub

• Press the F5 key on your keyboard or click on the Run sub button.

• You can see that the match is found in position 5.

How Does the VBA Code Work?

Method 2 – Use Excel VBA to Match Value from Another Worksheet

STEPS:

• Right-click on the sheet name and select View Code.

• Insert this VBA code.

VBA Code:

Sub example2_match()
Sheets("Result").Range("C5").Value = WorksheetFunction.Match(Sheets("Result").Range("C5").Value, Sheets("Data").Range("D5:D10"), 0)
End Sub

• Run the code by pressing the F5 key or clicking the Run Sub button.

• The result is found in the Result sheet.

Method 3 – Excel VBA Loops to Get Matched Value in Range

STEPS:

• Right-click on the sheet name and select View Code.

• Insert this code:

VBA Code:

Sub example3_match()
Dim i As Integer
For i = 5 To 8
Cells(i, 7).Value = WorksheetFunction.Match(Cells(i, 6).Value, Range("D5:D10"), 0)
Next i
End Sub

• Pressing the F5 key or clicking the Run Sub button will run the code.

• You will be able to see the result in Column G.

How Does the VBA Code Work?

Things to Keep in Mind

• If the match type is missing or not specified, it is assumed to be 1.
• If no match is detected, the field will be blank.
• The lookup value can be a numeric, character, or logical data, or a cell reference to a quantity, text, or logical significance.

Download Practice Workbook