by Svetlana Cheusheva, updated on
As you probably know, Microsoft Excel has three functions to look up a value - LOOKUP, VLOOKUP and HLOOKUP - and they seem to confuse users the most. In this tutorial, we will focus on the specificities of the Excel HLOOKUP function and discuss a few formula examples that will help you use it in Excel most efficiently.
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.
The HLOOKUP function in Excel has the following arguments:
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, HLOOKUP throws the #N/A error.
To make things easier to understand, you can translate Excel's HLOOKUP syntax:
into normal English:
To see how it works in practice, let's make a simple Hlookup example. Supposing you have a table with some basic information about the planets of our Solar system (please see the screenshot below). What you want is a formula that returns the diameter of the planet whose name is entered in cell B5.
In our Hlookup formula, we will be using the following arguments:
Now you put the arguments together and get the following formula:
=VLOOKUP(40, A2:B15,2)
Whenever you do a horizontal lookup in Excel, please remember the following facts:
As you already know, both functions VLOOKUP and HLOOKUP search for a lookup value. The difference is how the search is performed. As you probably noticed, the functions' names differ only in the first letter - "H" stands for horizontal, and "V" for vertical.
Hence, you use the VLOOKUP function to search is vertical lists when your lookup values are located in a column to the left of the data that you want to find.
The HLOOKUP function performs a horizontal lookup - it searches for a lookup value at the top-most row of the table and returns a value located a specified number of rows down in the same column.
The following image demonstrates the difference between Vlookup and Hlookup formulas in Excel:
Now that the HLOOKUP function has started to look a bit more familiar to you, let's discuss a few more formula examples to consolidate the knowledge.
As you already know, the HLOOKUP function in Excel can perform a lookup with exact and non-exact match depending on which value is supplied to the range_lookup argument:
Please keep in mind that although we say "approximate match", any Hlookup formula searches for an exact match in the first place. But setting the last argument to FALSE allows the formula to return an approximate match (the nearest value that is less than the lookup value) if an exact match is not found; TRUE or omitted returns the #N/A error in this case.
To better illustrate the point, consider the following HLOOKUP examples.
Supposing you have a list of planets in row 2 (B2:I2) and their temperatures in row 1(B1:I1). You want to find out which planet has a certain temperature that is input in cell B4.
You cannot rely on the off chance that your users know the lookup temperature exactly, so it makes sense to return a nearest match if an exact value in not found.
For instance, to find out the planet whose average temperature is around -340 °F, use the following formula (range_lookup set to TRUE or omitted as in this example):
=HLOOKUP(B4, B1:I2, 2)
Please remember that an approximate match requires sorting the values in the top row from smallest to largest or from A to Z, otherwise your Hlookup formula may return a wrong result.
As you can see in the screenshot below, our formula returns Uranus, one of the coldest planets in the Solar System maintaining an average of -346 degrees Fahrenheit.
If you know the lookup value exactly, you can set the last parameter of HLOOKUP to FALSE:
=HLOOKUP(B4, B1:I2, 2, FALSE)
On the one hand, an approximate match Hlookup is more user-friendly because it does not require sorting data in the first row. On the other hand, if the exact match is not found, an #N/A error will be returned.
Tip. Not to scare your users by N/A errors, you can embed your Hlookup formula in IFERROR and display your own message, for example:
=IFERROR(HLOOKUP(B4, B1:I2, 2, FALSE), "Sorry, nothing has been found")
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)
Tip. Instead of typing the workbook and worksheet names in the formula manually, you can select the cells in another sheet and Excel will add an external reference to your formula automatically.
As is the case with VLOOKUP, Excel's HLOOKUP function allows using the following wildcard characters in the lookup_value argument:
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)
Notes.
If you are writing a formula for a single cell, you may not worry about the proper use of relative and absolute cell references, either one will do.
Copying a formula to multiple cells is a different story. In essence:
To make things clearer, let's have a closer look at the formula that pulls data from another sheet:
=HLOOKUP(B$1, Diameters!$B$1:$I$2,2,FALSE)
In the above formula, we use absolute cell references ($B$1:$I$2) in table_array because it should remain constant when the formula is copied to other cells.
For lookup_value (B$1), we use a mixed reference, relative column and absolute row, because our lookup values (planet names) are in the same row (row 1) but in different columns (from B to I) and the column reference should change based on a relative position of a cell where the formula in copied.
Due to the clever use of cell references, our Hlookup formula works perfectly for multiple cells:
As you already know, the HLOOKUP function in Excel has a number of limitations, the most significant ones are its inability to look up anywhere except for the topmost row, and the necessary to sort values when searching with approximate match.
Luckily, there exists a more powerful and versatile alternative to Vlookup and Hlookup in Excel - the liaison of INDEX and MATCH functions, which boils down to this generic formula:
Assuming your lookup value is in cell B7, you are looking for a match in row 2 (B2:I2), and want to return a value from row 1 (B1:I1), the formula is as follows:
=INDEX(B1:I1,MATCH(B7,B2:I2,0))
In the screenshot below, you can see 2 Hlookup formulas that search in the first and second rows, and in both cases INDEX MATCH works equally well.
For the detailed explanation of the formula's logic and more examples, please see INDEX MATCH as a better alternative to VLOOKUP.
As mentioned in the beginning of this tutorial, the Excel HLOOKUP function is case insensitive. In situations when the character case matters, you can take the EXACT function that compares cells exactly, and put it inside the INDEX MATCH formula discussed in the previous example:
Assuming your lookup value is in cell B4, the lookup range is B1:I1, and the return range is B2:I2, the formula takes the following shape:
=INDEX(B2:I2, MATCH(TRUE, EXACT(B1:I1,B4),0))
Important note! It is an array formula and therefore you should press Ctrl + Shift + Enter to complete it.
The above example demonstrates my favorite but not the only possible way to do a case-sensitive Hlookup in Excel. If you are curious to know other techniques, please check out this tutorial: 4 ways to do a case-sensitive Vlookup in Excel. I think you won't have any problems with rebuilding the Vlookup formulas for a horizontal lookup.
By now you already know that Hlookup is a very useful and powerful lookup function in Excel. It is also a tricky one, and because of its numerous specificities #N/A, #VALUE or #REF errors are a common sight. If your HLOOKUP formula does not work properly, most likely it's because of one of the following reasons.
Even if you forget all other details about horizontal lookup in Excel, please remember this essential one - Hlookup can only search in the top-most row of the table. If case your lookup values reside in some other row, an N/A error is returned. To overcome this limitation, use an INDEX MATCH formula.
When doing a lookup in Excel, either horizontal (Hlookup) or vertical (Vlookup), in most cases you'd be searching for a particular thing, and therefore require an exact match. When searching with approximate match (range_lookup set to TRUE or omitted), remember to sort values in the first row in ascending order.
For more information and formula examples, please see Excel Hlookup with approximate and exact match.
When using multiple HLOOKUPs to retrieve information about a row of lookup values, you have to lock the table_array reference as demonstrated in Absolute and relative cell references in Hlookup formulas.
To understand why inserting a new row can break an Hlookup formula, remember how Excel HLOOKUP gets information about the lookup value - based on the row index number you specify.
Supposing, you want to get sales figures based on the product ID. Those figures are in row 4, so you type 4 in the row_index_num argument. But after a new row has been inserted, it becomes row 5... and your Hlookup stops working. The same problem may occur when deleting an existing row from the table.
The solution is either lock the table to prevent your users from inserting new rows, or use INDEX & MATCH instead of Hlookup. In Index/Match formulas, you specify the rows to look up and to return values from as range references, not index numbers, and Excel is smart enough to adjust those references on the fly. So, you are free to delete or insert as many columns and rows as you want without worrying about updating every formula in your worksheet.
The HLOOKUP function in Excel can only return one value, which is the first value in the table that matches the lookup value.
If there are a few identical records in your table, choose one of the following solutions that best suits your needs:
When your obviously correct Hlookup formula returns a bunch of #N/A errors, check your table and lookup value for extra spaces. You can quickly remove leading, trailing and excess in-between spaces by using Excel TRIM function or our Cell Cleaner tool.
Text strings that look like numbers are another stumbling block for Excel formulas. The detailed description of this issue and possible solutions are described in Why Excel formulas may stop working.
All Lookup functions in Excel work only as long as a lookup value is under 255 characters. A longer lookup value results in the #VALUE! error. Since the INDEX /MATCH formula is free of this limitation, use it to overcome this hurdle.
If you perform h-lookup from another workbook, remember to supply the full path to it. A couple of formula examples can be found here: How to do Hlookup from another worksheet or workbook.
It has already been highlighted more than once that HLOOKUP is a demanding function that should be treated with great care. Below are a few most common errors caused by supplying incorrect arguments:
Well, this is how to use HLOOKUP in Excel. Hopefully you will find this information helpful. I thank you for reading and hope to see you on our blog next week!
Table of contents
