HLOOKUP is a function that's symmetrical to VLOOKUP. We've explained how VLOOKUP works in a previous blog post. HLOOKUP requires similar arguments, but it references rows instead of columns.
If your spreadsheet has a lot of columns, you may find HLOOKUP to be more useful than VLOOKUP. Here's how it works.
Arguments are required inputs for a function to return a desired value. HLOOKUP has four arguments:
- lookup_value: the focus of your search. It could be a place, a person's name, or a product category. HLOOKUP will retrieve information from the lookup_value's column.
- table_array: this defines which part of your spreadsheet you want the function to search.
- row_index_num: this tells Excel what row to search for values that match your lookup_value's column.
- range_lookup gives Excel instructions on what to return if it can't find anything. (This argument is optional.)
We'll walk through these steps with the same example dataset we used in the VLOOKUP post: a list of disease cases in a set of counties. For our new example, let’s search for West Nile cases in County 2. The HLOOKUP function we’ll need is:
=HLOOKUP(C3, B3:E7, 4, FALSE)
Step 1: Select your lookup_value
The lookup_value for HLOOKUP references the first row of the array (instead of the first column, as in VLOOKUP). We’re searching for the label County 2, so we enter C3 for our first argument.
Step 2: Define your table_array
The table_array still includes the data and labels. But to capture the labels, row 3 is included instead of a column A. To capture all the data necessary for our arguments, we select an array of B3:E7.
Step 3: Input the row_index_num
The third argument row_index_num, is similar to VLOOKUP’s third argument, but requires us to find the relevant row instead of a column. In the example, the fourth row of our array corresponds with West Nile so we enter 4 for our argument.
Just like on VLOOKUP, this number is relative, not absolute—find it by counting down from the top row of your selected array.
Step 4: The optional range_lookup.
The fourth argument, range_lookup, is the same as VLOOKUP’s fourth argument. In this example we enter FALSE and get a final answer of 8 West Nile cases in County 2.
Once again, this example is extremely simplified. When you're dealing with dozens or hundreds of columns, HLOOKUP can be a huge time-saver.