How to Use VBA HLookup Function in 3 Minutes (Excel)

Written by Kasper Langmann

The VBA HLookup function is a powerful tool in Excel that allows you to search for specific data in a horizontal table or array. This function is particularly useful when dealing with large datasets where manual searching would be time-consuming and inefficient. In this guide, we will explore how to use the VBA HLookup function effectively and efficiently.

Understanding the VBA HLookup Function

The VBA HLookup, or Horizontal Lookup, function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

It works by searching for a value in the top row of a table array and returns a value in the same column from a row you specify. The “H” in HLookup stands for “horizontal”, and lookup values must appear in a row across the top of the table, with data columns beneath them.

Function Syntax

The syntax for the HLookup function in VBA is:

HLookup(lookup_value, table_array, row_index, [range_lookup])

Where the function arguments are:

  • lookup_value: The value to search in the first row of the table array.
  • table_array: Two or more rows of data. The first row is searched for the key and the cell in the same column in the row_index row is returned.
  • row_index: The row position in the table from which to return a value. If row_index is less than 1, the HLookup function will return #VALUE!. If row_index is greater than the number of rows on the table_array, HLookup returns the #REF! error value.
  • range_lookup: A logical value that specifies whether you want HLookup to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLookup will find an exact match. If an exact match is not found, the error value #N/A is returned.

Implementing the VBA HLookup Function

Now that we understand the syntax and function arguments, let’s dive into how to implement the VBA HLookup function in Excel.

First, you need to open the Visual Basic for Applications editor by pressing Alt + F11. Then, you can insert a new module by navigating to Insert > Module. This is where you will write your VBA HLookup function.

Step-by-Step Guide

Here is a step-by-step guide on how to use the VBA HLookup function:

  1. Identify the lookup_value. This is the value that you want to search for in the first row of your table_array.
  2. Identify the table_array. This is the range of cells that contains the data you want to search.
  3. Identify the row_index. This is the row number in your table_array from which the matching value should be returned.
  4. Identify the range_lookup. Decide whether you want an approximate match (TRUE or omitted) or an exact match (FALSE).
  5. Write your VBA HLookup function using the syntax we discussed earlier. For example: 
    result = Application.WorksheetFunction.HLookup(lookup_value, table_array, row_index, range_lookup)
  6. Run your VBA code by pressing F5 or clicking on Run > Run Sub/UserForm.

Common Errors and Troubleshooting

While the VBA HLookup function is a powerful tool, it is not without its potential pitfalls. Here are some common errors that you might encounter and how to troubleshoot them:

#N/A Error

This error occurs when the HLookup function cannot find the lookup_value you specified. This could be due to a typo, or because the range_lookup argument is set to FALSE and an exact match cannot be found. To resolve this, check your lookup_value and range_lookup argument.

#VALUE! Error

This error occurs when the row_index argument is less than 1. Remember, the row_index argument refers to the row number in your table_array from which the matching value should be returned. Make sure this number is 1 or greater.

#REF! Error

This error occurs when the row_index argument is greater than the number of rows in the table_array. To resolve this, ensure that your row_index argument does not exceed the number of rows in your table_array.

Conclusion

The VBA HLookup function is a versatile and powerful tool that can greatly enhance your data analysis capabilities in Excel. By understanding its syntax and function arguments, and knowing how to troubleshoot common errors, you can use this function to efficiently search for and retrieve data from large datasets. Remember to practice using this function regularly to become more comfortable and proficient with it.