HLOOKUP Function in Excel

In the previous blog post we learned about VLOOKUP function in Excel. In this blog post, we will explain how the HLOOKUP function works, what its syntax is, what are its advantages and limitations, and how to use it with some examples. HLOOKUP stands for horizontal lookup and it allows you to search for a value in the first row of a table and return a value from the same column in another row.

The syntax of the HLOOKUP function is:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The arguments of the function are:

  • lookup_value: The value that you want to find in the first row of the table. This can be a number, text, logical value, or a cell reference.
  • table_array: The range of cells that contains the table or array of data. The first row of this range should contain the lookup values.
  • row_index_num: The row number in the table from which you want to return a value. This can be a positive integer or a cell reference.
  • [range_lookup]: (Optional argument) 
    • If you omit this argument or set it to TRUE, the function will perform an approximate match and find the closest value that is less than or equal to the lookup value. 
    • If you set this argument to FALSE, the function will perform an exact match and find only the exact value that matches the lookup value.

Advantages of HLOOKUP Function

  • It can handle horizontal data more efficiently than VLOOKUP, which is designed for vertical data.
  • It can return values from any row in the table, not just the rightmost column like VLOOKUP.
  • It can use wildcards (* and ?) to find partial matches, which can be useful when dealing with text data.

Limitations of HLOOKUP Function

  • It requires that the lookup values are sorted in ascending order otherwise, it may return incorrect results or an error.
  • It cannot handle multiple criteria or complex conditions like SUMIFS or COUNTIFS functions.
  • It cannot look up values to the left of the lookup value.
  • It may slow down your workbook if you use it on large data sets.

Difference between HLOOKUP and VLOOKUP:

HLOOKUPVLOOKUP
Searches for a value in the first row of a tableSearches for a value in the first column of a table
Returns a value from the same column in another rowReturns a value from the same row in another column
Performs a horizontal lookupPerforms a vertical lookup
Requires a horizontal tableRequires a vertical table
Less popular and less usedMore popular and more used
Cannot look up values to the left of the lookup valueCan look up values to the left of the lookup value

Case 1: Exact Match

In this table of sales data for different products and regions, we can find the sales amount for any product in any region. You can use the following formula:

=HLOOKUP($I$1,$A$1:$E$5,IFS(H2=A2,2,H2=A3,3,H2=A4,4,H2=A5,5),FALSE)

Note: Make sure to always convert your data to table in excel by selecting the data range and then press CTRL+T. So that the HLOOKUP formula auto-updates when we make a new entry in our data range.

Case 2: Approximate Match

The HLOOKUP function supports wildcard matching. HLOOKUP is case-sensitive, the lookup value should match exactly with the value in our data table. However, if we don't have the exact value, we can use a wildcard. Wildcards are particularly useful when you're unsure of the exact and full name for which you want information. When using wildcard characters like "*" or "?",  as a text argument they must be enclosed within double quotation marks (" ") in Excel.

In this example, if Mr. Shyaam knows that his data is in row 3 of the Excel table below. He wants to know his commission rate, but he doesn't know whether the name of the column is "Commission Rate", "% Rate", or simply "Rate". In this case he can use wildcard matching formula as shown below to return his commission rate regardless of what the actual name of the column is:

=HLOOKUP("*Rate", A1:G7, 3, FALSE)

Conclusion:

In this blog post, we have learned about HLOOKUP. We have seen how this function can help us find the information we need from a table of data. I hope that this blog post has helped you understand the logic behind this function. Thank you for reading and happy learning!

Comments

Post a Comment

Popular posts from this blog

XLOOKUP Function in Excel

Image
In our previous blog post we have already learned about 2 most useful lookup functions: VLOOKUP and HLOOKUP . In this blog post, we’ll explore what XLOOKUP is, its syntax, why you should use it, its pros and cons, and provide some practical examples. The  XLOOKUP  function revolutionize the way we search and retrieve data. XLOOKUP function is a modern and versatile replacement for traditional lookup functions in Excel. It allows you to find things in a table or range by row, regardless of whether the return column is on the left or right side. It’s like a search engine for your Excel data. Here’s what you need to know: Syntax : =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Arguments : lookup_value : The value you want to search for. lookup_array : The range or array to search. return_array : The range or array from which to return results. [ if_not_found ] : (Optional) Specify what to return if no match is found. [ match_...
Read more

Introduction to Pivot Table in Excel - Advanced

Image
In the previous blog post, we learned the  Basics of Pivot Table  in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time. Slicers Slicers are used to filter your data by selecting specific values from a field. For example in this data table , we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products.  Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer. A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections. You c...
Read more

Introduction to Pivot Table in Excel - Basics

Image
Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...
Read more