Your SEO optimized title page contents

How to use Hlookup

How to use Hlookup in excel

The Excel HLOOKUP function is designed for horizontal lookup. More specifically, it searches for a certain value in the first row of the table and returns another value in the same column from a row that you specify.

The HLOOKUP function is available in all versions of Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 and lower.

Excel HLOOKUP syntax and uses
The HLOOKUP function in Excel has the following arguments:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Lookup_value (required) – the value to search for. It can be a cell reference, numeric value or text string.
Table_array (required) – two or more rows of data in which the lookup value is searched. It can be a regular range, named range or Excel table. Lookup values should always be located in the first row of table_array.
Row_index_num (required) – the row number in table_array from which the value should be returned. For example, to return the matching value from the 2nd row, set row_index_num to 2, and so on.
Range_lookup (optional) – a logical (Boolean) value that instructs HLOOKUP to search with exact or approximate match.
If TRUE or omitted, an approximate match is returned. What it means is if an exact match is not found, your Hlookup formula will do a non-exact match and return the next largest value that is less than lookup_value.

If FALSE, only an exact match is returned. If no value in a specified row matches the lookup value exactly, a Hlookup formula returns the #N/A error.

To make things easier to understand, you can translate Excel’s HLOOKUP syntax:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
into normal English:

HLOOKUP(search for this value, in this table, return a value from this row, [return an approximate or exact match])

 

 

In our Hlookup formula, we will be using the following arguments:

Lookup_value is B5 – the cell containing the planet name you want to find.
Table_array is B2:I3 – the table where the formula will look up the value.
Row_index_num is 2 because Diameter is the 2nd row in the table.
Range_lookup is FALSE. Because the first row of our table is not sorted from A to Z, we can only look up with exact match, which works just fine in this example.
Now you put the arguments together and get the following formula:

=HLOOKUP(40, A2:B15,2)

How to do Hlookup from another worksheet or workbook

In general, h-lookup from another sheet or a different workbook means nothing else than supplying external references to your HLOOKUP formula.

To pull out matching data from a different worksheet, you specify the sheet name followed by an exclamation mark. For example:

=HLOOKUP(B$1, Diameters!$B$1:$I$2,2,FALSE)

If the worksheet name contains spaces or non-alphabetical characters, enclose the name in single quotation marks, like this:

=HLOOKUP(B$1, ‘Planet diameters’!$B$1:$I$2,2,FALSE)

When referencing another workbook, include the workbook name enclosed in square brackets:

=HLOOKUP(B$1, [Book1.xlsx]Diameters!$B$1:$I$2, 2, FALSE)

If you are pulling data from a closed workbook, the entire path should be specified:

=HLOOKUP(B$1, ‘D:\Reports\[Book1.xlsx]Diameters’!$B$1:$I$2, 2, FALSE)

Excel HLOOKUP with partial match (wildcard characters)
As is the case with VLOOKUP, Excel’s HLOOKUP function allows using the following wildcard characters in the lookup_value argument:

Question mark (?) to match any single character
Asterisk (*) to match any sequence of characters
Wildcards come in handy when you want to pull information from a database based on some text that is part of the lookup cell’s contents.

For example, you have a list of customer names in row 1 and order IDs in row 2. You want to find the order id for a specific customer but you cannot remember the customer name exactly, though you do remember it begins with “ace”.

Assuming your data are in cells B1:I2 (table_array) and order numbers are in row 2 (row_index_num), the formula goes as follows:

=HLOOKUP(“ace*”, B1:I2, 2, FALSE)

To make the formula more flexible, you can type the lookup value in a special cell, say B4, and concatenate that cell with the wildcard character, like this:

=HLOOKUP(B4&”*”, B1:I2, 2, FALSE)

Learn excel from us. A step by step tutorials to make you up-skill from beginner to pro. You can visit our main page to know more.