How to use HLOOKUP in Excel?

The HLOOKUP (Horizontal Lookup) function in Excel is a powerful tool that allows you to search for a value in the top row of a table and return the value in the same column from a specified row. Unlike VLOOKUP (Vertical Lookup), which searches vertically down a column, HLOOKUP searches horizontally across the top row.

In this blog, we’ll explore the HLOOKUP function in detail, providing a practical example using some sales data.

 

Syntax of HLOOKUP

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

 

lookup_value: The value you want to search for in the first row of the table.

table_array: The table or range of data where the lookup will happen.

row_index_num: The row number in the table from which the value should be returned (the first row is 1).

range_lookup (optional): If TRUE (or omitted), an approximate match will be found; if FALSE, an exact match is required.

 

Here is some sales data of 5 Items for a year.

Sales Data

S.No	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	Pen	120	110	140	130	150	160	145	155	135	125	140	150
2	Pencil	90	85	100	95	105	110	98	102	110	120	100	115
3	Eraser	75	80	90	85	95	85	100	92	80	85	95	105
4	Notebook	200	180	220	210	230	225	240	230	220	210	225	250
5	Sharpener	50	55	60	52	65	70	68	72	75	65	60	70

 

Scenario 1: Finding the Number of Notepad sold in Jul

1. Lookup Value: Jul.
2. Table Array: Our data is now in A1:N6.
3. Row Index Number: The Notepad is stored in the 5th row.
4. Range Lookup: Use FALSE for an exact match.

The formula would be:

=HLOOKUP("Jul", A1:N6, 5, FALSE)

This will return the number of Notepad sold in Jul Month.

 

S.No	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	Pen	120	110	140	130	150	160	145	155	135	125	140	150
2	Pencil	90	85	100	95	105	110	98	102	110	120	100	115
3	Eraser	75	80	90	85	95	85	100	92	80	85	95	105
4	Notebook	200	180	220	210	230	225	240	230	220	210	225	250
5	Sharpener	50	55	60	52	65	70	68	72	75	65	60	70

 

 

 

Scenario 2: Finding the Number of Pen sold in Dec

1. Lookup Value: Dec.
2. Table Array: Our data is now in A1:N6.
3. Row Index Number: The Pen is stored in the 2nd row.
4. Range Lookup: Use FALSE for an exact match.

The formula would be:

=HLOOKUP("Dec", A1:N6, 2, FALSE)

This will return the number of Pen sold in Jul Month.

 

S.No	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	Pen	120	110	140	130	150	160	145	155	135	125	140	150
2	Pencil	90	85	100	95	105	110	98	102	110	120	100	115
3	Eraser	75	80	90	85	95	85	100	92	80	85	95	105
4	Notebook	200	180	220	210	230	225	240	230	220	210	225	250
5	Sharpener	50	55	60	52	65	70	68	72	75	65	60	70

 

 

Final Remarks

The HLOOKUP function in Excel is a powerful tool for looking up data arranged horizontally, making it useful when your information is structured with row-based labels. While it may not be as frequently used as VLOOKUP, it is invaluable when working with horizontal data layouts, such as timelines, monthly reports, or data categories across rows.

Key takeaways for using HLOOKUP effectively:

1. Structure Matters: HLOOKUP works best when your data is organized in rows with the lookup value in the first row.
2. Flexibility: You can use HLOOKUP for a variety of tasks like looking up sales figures, employee records, or product details over time, where row labels are key, if such data is available.
3. Exact vs Approximate Match: Use FALSE for an exact match, which is generally more reliable for finding specific values. TRUE can be used for approximate matches when needed.

While HLOOKUP is ideal for row-based searches, it's essential to choose the right lookup function based on your data's structure. If your data is vertical (like in most cases), VLOOKUP or XLOOKUP might be more appropriate. Nonetheless, mastering HLOOKUP expands your ability to work efficiently with a wide range of data layouts in Excel.