How do you use the
function in Excel properly and effectively?
As someone who frequently uses Excel, I understand how important it is to master various functions and features to enhance productivity and effectiveness. One such function that can be incredibly useful when working with large data sets is the
In this article, I’ll share a few examples to demonstrate how this function can improve your data analysis skills.
For those unfamiliar with the
function, it stands for
. This function searches for a specified value in the
of a table or an array of values and then returns the corresponding value in a
within the table. It can save time and effort, especially when comparing or retrieving data from different sources or spreadsheets.
What Is HLOOKUP?
As a regular user of Excel spreadsheets, I often encounter situations where I need to search for specific information within a sheet. One useful function that helps me with this task is
and I find this function particularly helpful when obtaining data from a specific year for an item.
Most data sets I handle are financial, with the top horizontal axis representing the year, so if I need to calculate rates based on the number of sales and total sales of a product in a particular year I have found the
function a handy formula to use.
Excel HLOOKUP Syntax
The Horizontal Lookup function is a powerful spreadsheet function that allows you to search for data in horizontal rows, based on a specific reference value.
The syntax for the
function is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Here’s a brief explanation of each argument:
lookup_value: This is the value you want to search for within the
table_array: The table range where you’ll be searching for the
lookup_value. This is typically a set of rows containing data.
row_index_num: The row number you want to return the data from within the
table_array. The first row containing the
range_lookup: This is an optional argument that specifies whether you want to enable an approximate match for the
lookup_value. If set to
TRUEor omitted, Excel will return an approximate match. If set to
FALSE, Excel will search for an exact match.
Suppose you have the following sheet with the following financial totals representing the Sales of each product per year, like so:
With this type of data set if I need to fetch the total sales for a particular widget according to year, then I can use the
formula as follows:
What will this formula return?
This formula will fetch the Sales in 2021 for Widget A.
lookup_valueis the year
table_arrayis the range
row_index_numis the number
3, representing the row
FALSE, meaning an exact match of
2021) needs to be found in the first row
in the range
(which it finds in the first cell
). With this value found, it then counts down
rows and returns the value
What would happen if some of the values in the above
formula were changed?
What would the following formula return?
What value did you get?
Stepping through this formula the answer you would get is as follows:
Search for the value
2023in the first row of the table
This value is found in cell
From this cell count
5rows with the current row in
Therefore, the answer is the value found in cell
This formula is therefore returning the total sales of Widget C in 2023.
Another HLOOKUP Example
Another way I have used the
formula is by obtaining specific assessment results of students.
Assume I have performed a few tests on my students and the results of these students are recorded below as follows:
|1||Student||Test 1||Test 2||Test 3|
If I want to find the score of
, I can use the
function like this:
The result will be
, which is Bob’s score in Test 2.
Advanced HLOOKUP: Tips And Tricks
When working with
in Excel there are a couple of things I am more aware of when using the formula than I would otherwise be when using
. And the biggest aspect is that the rows which search for the value I want may not be static.
Have a look at the following example with my original widget sale table, and watch how easy it is to break the original
There are a couple of ways I’ve circumvented these problems when dealing with
HLOOKUP Returns Final Row
formula will always return the last row in the formula, in the third parameter of the
function wrap the same
This helps, especially when more rows are likely to be inserted (or deleted) above the row you’re seeking to return.
Here’s how this would look if I my formula was seeking 2021 for Widget C with the above data:
Dynamic HLOOKUP Row
What if the row being returned can move? In this case, you would need to search for the row and have it return its location. For this use
Here’s how this formula looks:
Notice in the above when the actual row is moved it does cause the
Common HLOOKUP Errors
Besides working around possible issues when dealing with
formulas, there are still common lookup errors that can also happen.
This error occurs when
can’t find a match for the lookup value in the specified row. To fix this, double-check for typos or formatting disparities between the lookup value and the table array. Using the
functions can help to display a specific message or value if there’s no match.
=IFERROR(HLOOKUP(value, table, row_index, TRUE), "Not Found")
||Lookup value not found||
Double-check spelling and formatting. Wrap
This error occurs when the row index number in
is less than 1, or not an integer.
To fix this, ensure that the row index number is a valid integer and greater than or equal to 1.
||Row index number invalid||Ensure row index number is a valid integer and greater than or equal to 1.|
This error occurs when the row index number in
is greater than the number of rows in the table array.
To fix this, review the table array and reduce the row index number accordingly.
If you want to know what the maximum number of rows available in your
use the formula
||Row index number out of range||Reduce the row index number to match the table array’s size.|
Finally, it’s worth mentioning that using
in the lookup row can lead to inaccurate results or the return of the first match encountered. To maximise the effectiveness of
, ensure that the lookup row contains unique values if the fourth parameter is set to
will likely be the response).
HLOOKUP Examples: Summary
In this article, I’ve shared some examples of using the
function in Excel. Through these examples, I hope you’ve gained a better understanding of this useful tool and how it can help in various situations.
can be a valuable addition to your Excel toolkit, don’t forget to explore other functions and features Excel has to offer. There’s a wealth of tools available to make your work more manageable and help you analyse data more effectively. Happy spreadsheeting!