How to Use HLOOKUP in VBA (With Examples)

You can use the following basic syntax to perform a HLOOKUP using VBA:

Sub Hlookup()
    Range("H2").Value = WorksheetFunction.HLookup(Range("G2"),Range("A1:E2"),2,False)
End Sub

This particular example looks up the value in cell G2 in the columns of A1:E2 and finds the value located in the second row of that specific column and then assigns the result to cell H2.

Note: The last argument of False specifies that we want to find an exact match.

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

Example: How to Use HLOOKUP in VBA

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

Suppose we would like to look up the team name “Mavs” in the dataset and return the corresponding value in the points row.

We can create the following macro to do so:

Sub Hlookup()
    Range("H2").Value = WorksheetFunction.HLookup(Range("G2"),Range("A1:E2"),2,False)
End Sub

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

The macro correctly returns a value of 22 points for the Mavs.

If we change the name of the team in cell G2 and then run the macro again, it will correctly find the points value for the new team name.

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

The macro correctly returns a value of 34 points for the Rockets.

Note: You can find the complete documentation for the VBA HLookup method here.

Additional Resources

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

VBA: How to Use INDEX MATCH
VBA: How to Use VLOOKUP
VBA: How to Use XLOOKUP