How To Use Hlookup In Excel 2016
As you probably know, Microsoft Excel has three functions to look upward a value - LOOKUP, VLOOKUP and HLOOKUP - and they seem to confuse users the nigh. In this tutorial, we will focus on the specificities of the Excel HLOOKUP function and discuss a few formula examples that volition assist yous utilise it in Excel nearly efficiently.
What is HLOOKUP in Excel?
The Excel HLOOKUP function is designed for horizontal lookup. More specifically, it searches for a certain value in the commencement row of the tabular array and returns some other value in the same column from a row that you lot specify.
The HLOOKUP function is bachelor 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 information in which the lookup value is searched. It can exist a regular range, named range or tabular array. Lookup values should always be located in the get-go row of table_array.
- Row_index_num (required) - the row number in table_array from which the value should exist returned. For case, to render the matching value from the 2nd row, set row_index_num to 2, then on.
- Range_lookup (optional) - a logical (Boolean) value that instructs HLOOKUP to search with exact or guess friction match.
If Truthful or omitted, an approximate match is returned. What information technology ways is if an verbal match is non institute, your Hlookup formula will exercise a non-exact friction match and return the adjacent 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 tin translate Excel'south HLOOKUP syntax:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
into normal English:
HLOOKUP(search for this value, in this table, render a value from this row, [render an approximate or exact match])
To see how it works in practise, permit's make a simple Hlookup example. Supposing yous have a table with some basic information about the planets of our Solar organisation (please see the screenshot below). What you want is a formula that returns the diameter of the planet whose proper noun is entered in cell B5.
In our Hlookup formula, we will be using the post-obit arguments:
- Lookup_value is B5 - the cell containing the planet name yous want to observe.
- Table_array is B2:I3 - the tabular array where the formula volition look up the value.
- Row_index_num is ii because Bore is the twond 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 verbal match, which works only fine in this example.
Now y'all put the arguments together and get the following formula:
=VLOOKUP(40, A2:B15,2)
three things you should know most Excel HLOOKUP function
Whenever you lot exercise a horizontal lookup in Excel, please remember the following facts:
- The HLOOKUP function can only search in the top-most row of table_array. If y'all need to look up somewhere else, consider using an Index / Match formula.
- HLOOKUP in Excel is case-insensitive, it does not distinguish uppercase and lowercase.
- If range_lookup is set to TRUE or omitted (approximate lucifer), the values in the showtime row of table_array must be sorted in ascending order (A-Z) left to right.
What is the difference betwixt VLOOKUP and HLOOKUP in Excel?
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 but in the commencement letter - "H" stands for horizontal, and "5" for vertical.
Hence, you employ the VLOOKUP office to search is vertical lists when your lookup values are located in a cavalcade to the left of the data that yous want to find.
The HLOOKUP office 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 downwardly in the same column.
The following image demonstrates the difference between Vlookup and Hlookup formulas in Excel:
How to use HLOOKUP in Excel - formula examples
Now that the HLOOKUP function has started to await a bit more familiar to you, let's discuss a few more formula examples to consolidate the knowledge.
Horizontal lookup with approximate and exact match
Equally you already know, the HLOOKUP function in Excel can perform a lookup with exact and not-exact match depending on which value is supplied to the range_lookup statement:
- Truthful or omitted - approximate match
- FALSE - exact match
Please go along in mind that although nosotros say "guess friction match", whatever Hlookup formula searches for an exact match in the first place. But setting the concluding argument to Simulated allows the formula to render an gauge lucifer (the nearest value that is less than the lookup value) if an exact match is non institute; TRUE or omitted returns the #N/A error in this case.
To better illustrate the point, consider the following HLOOKUP examples.
HLOOKUP with approximate match
Supposing y'all accept a list of planets in row 2 (B2:I2) and their temperatures in row 1(B1:I1). Y'all want to find out which planet has a sure temperature that is input in cell B4.
You cannot rely on the off take chances that your users know the lookup temperature exactly, so information technology makes sense to return a nearest match if an exact value in not found.
For instance, to detect out the planet whose average temperature is around -340 °F, utilise the post-obit formula (range_lookup gear up to Truthful or omitted as in this instance):
=HLOOKUP(B4, B1:I2, 2)
Please remember that an gauge match requires sorting the values in the meridian row from smallest to largest or from A to Z, otherwise your Hlookup formula may render a incorrect effect.
As you lot tin can meet in the screenshot below, our formula returns Uranus, 1 of the coldest planets in the Solar Organization maintaining an average of -346 degrees Fahrenheit.
HLOOKUP with exact match
If you know the lookup value exactly, you can prepare the last parameter of HLOOKUP to Fake:
=HLOOKUP(B4, B1:I2, ii, FALSE)
On the one manus, an approximate match Hlookup is more user-friendly because it does not require sorting data in the get-go row. On the other paw, if the exact match is not establish, an #Northward/A error will be returned.
Tip. Non to scare your users by Due north/A errors, you lot can embed your Hlookup formula in IFERROR and display your own message, for example:
=IFERROR(HLOOKUP(B4, B1:I2, ii, FALSE), "Sorry, nothing has been found")
How to do HLOOKUP from another worksheet or workbook
In general, h-lookup from another sail or a different workbook means aught else than supplying external references to your HLOOKUP formula.
To pull out matching information from a different worksheet, you specify the sheet name followed by an exclamation mark. For case:
=HLOOKUP(B$1, Diameters!$B$ane:$I$two,two,FALSE)
If the worksheet name contains spaces or not-alphabetical characters, enclose the proper name in single quotation marks, like this:
=HLOOKUP(B$1, 'Planet diameters'!$B$1:$I$2,two,Fake)
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$i, 'D:\Reports\[Book1.xlsx]Diameters'!$B$ane:$I$2, ii, Imitation)
Tip. Instead of typing the workbook and worksheet names in the formula manually, you tin can select the cells in another sheet and Excel will add an external reference to your formula automatically.
Excel HLOOKUP with partial match (wildcard characters)
Equally is the instance with VLOOKUP, Excel'southward HLOOKUP function allows using the following wildcard characters in the lookup_value argument:
- Question marker (?) to lucifer any single graphic symbol
- Asterisk (*) to match any sequence of characters
Wildcards come up in handy when you desire to pull information from a database based on some text that is part of the lookup cell'south contents.
For example, y'all have a list of customer names in row 1 and lodge IDs in row ii. Yous want to discover the order id for a specific customer simply you cannot remember the customer name exactly, though you do recall information technology begins with "ace".
Assuming your information are in cells B1:I2 (table_array) and order numbers are in row 2 (row_index_num), the formula goes equally follows:
=HLOOKUP("ace*", B1:I2, 2, FALSE)
To make the formula more flexible, you tin can type the lookup value in a special prison cell, say B4, and concatenate that cell with the wildcard character, like this:
=HLOOKUP(B4&"*", B1:I2, 2, FALSE)
Notes.
- For a wildcard HLOOKUP formula to work correctly, the range_lookup argument needs to be fix to Simulated.
- If table_array contains more than than ane value that meets the wildcard criteria, the get-go establish value is returned.
Accented and relative cell references in HLOOKUP formulas
If you are writing a formula for a single cell, you may not worry nigh 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:
- You should always set up table_array past using accented cell references with the dollar sign ($) like $B$1:$I$2.
- Typically, the lookup_value reference is relative or mixed depending on your business logic.
To make things clearer, let'due south have a closer expect at the formula that pulls data from another sheet:
=HLOOKUP(B$one, Diameters!$B$i:$I$two,ii,FALSE)
In the in a higher place formula, we use accented jail cell references ($B$1:$I$2) in table_array because it should remain abiding when the formula is copied to other cells.
For lookup_value (B$1), we utilize 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:
Index/Lucifer - a more powerful alternative to Excel HLOOKUP
As yous already know, the HLOOKUP function in Excel has a number of limitations, the well-nigh 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, in that location exists a more powerful and versatile alternative to Vlookup and Hlookup in Excel - the liaison of INDEX and Lucifer functions, which boils down to this generic formula:
Index (where to render a value from, MATCH (lookup value , where to search, 0))
Bold your lookup value is in cell B7, you are looking for a friction match in row 2 (B2:I2), and want to render a value from row 1 (B1:I1), the formula is as follows:
=INDEX(B1:I1,Friction match(B7,B2:I2,0))
In the screenshot below, yous can run across 2 Hlookup formulas that search in the beginning and 2d rows, and in both cases Alphabetize MATCH works equally well.
For the detailed explanation of the formula's logic and more examples, please see Index MATCH as a ameliorate alternative to VLOOKUP.
How to do instance-sensitive h-lookup in Excel
As mentioned in the beginning of this tutorial, the Excel HLOOKUP function is case insensitive. In situations when the graphic symbol case matters, yous can take the Exact office that compares cells exactly, and put it inside the INDEX MATCH formula discussed in the previous example:
INDEX (row to render a value from, MATCH(True, EXACT(row to search in, lookup value) , 0))
Assuming your lookup value is in cell B4, the lookup range is B1:I1, and the render range is B2:I2, the formula takes the post-obit shape:
=Index(B2:I2, MATCH(True, Exact(B1:I1,B4),0))
Important note! Information technology is an array formula and therefore you should press Ctrl + Shift + Enter to complete it.
The above case demonstrates my favorite only not the only possible way to exercise a case-sensitive Hlookup in Excel. If you are curious to know other techniques, please check out this tutorial: 4 means to practice a instance-sensitive Vlookup in Excel. I call back y'all won't accept whatever problems with rebuilding the Vlookup formulas for a horizontal lookup.
Top 10 reasons why Excel HLOOKUP is not working
Past now you already know that Hlookup is a very useful and powerful lookup role in Excel. It is also a tricky one, and because of its numerous specificities #Due north/A, #VALUE or #REF errors are a common sight. If your HLOOKUP formula does not work properly, almost probable it's because of i of the following reasons.
1. HLOOKUP in Excel cannot look above itself
Even if you forget all other details about horizontal lookup in Excel, please recall this essential 1 - Hlookup can only search in the top-most row of the table. If instance your lookup values reside in some other row, an N/A error is returned. To overcome this limitation, apply an Alphabetize Lucifer formula.
2. Approximate lucifer vs. exact match
When doing a lookup in Excel, either horizontal (Hlookup) or vertical (Vlookup), in most cases yous'd be searching for a particular thing, and therefore crave an exact match. When searching with approximate friction match (range_lookup set to TRUE or omitted), recollect to sort values in the commencement row in ascending order.
For more information and formula examples, delight run across Excel Hlookup with approximate and exact match.
3. The table array reference changes when copying the formula
When using multiple HLOOKUPs to retrieve information about a row of lookup values, yous accept to lock the table_array reference as demonstrated in Absolute and relative cell references in Hlookup formulas.
4. Inserting or deleting a new row
To understand why inserting a new row tin can interruption an Hlookup formula, recollect how Excel HLOOKUP gets information about the lookup value - based on the row index number you specify.
Supposing, yous 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 afterward 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 tabular array.
The solution is either lock the table to forestall your users from inserting new rows, or apply Index & MATCH instead of Hlookup. In Index/Match formulas, you specify the rows to expect up and to return values from as range references, not index numbers, and Excel is smart enough to adjust those references on the wing. So, you are gratuitous to delete or insert as many columns and rows as yous desire without worrying well-nigh updating every formula in your worksheet.
5. Duplicates in the table
The HLOOKUP function in Excel can but return one value, which is the first value in the table that matches the lookup value.
If in that location are a few identical records in your tabular array, choose one of the following solutions that all-time suits your needs:
- Remove duplicates by using Excel's ways or our Duplicate Remover
- If duplicate records should be kept in the dataset, create a PivotTable to group and filter your data the way you want.
- Employ an array formula to extract all duplicate values in the lookup range.
half-dozen. Extra spaces
When your obviously correct Hlookup formula returns a bunch of #Due north/A errors, check your table and lookup value for extra spaces. You can quickly remove leading, trailing and excess in-betwixt spaces by using Excel TRIM function or our Cell Cleaner tool.
7. Numbers formatted as text
Text strings that look like numbers are another stumbling cake for Excel formulas. The detailed description of this issue and possible solutions are described in Why Excel formulas may stop working.
eight. Lookup value exceeds 255 characters
All Lookup functions in Excel work only equally long as a lookup value is nether 255 characters. A longer lookup value results in the #VALUE! error. Since the Index /Lucifer formula is free of this limitation, utilise it to overcome this hurdle.
nine. Total path to the lookup workbook is non specified
If you perform h-lookup from another workbook, remember to supply the total path to information technology. A couple of formula examples tin can exist plant hither: How to do Hlookup from some other worksheet or workbook.
10. Wrong arguments
It has already been highlighted more than once that HLOOKUP is a demanding part that should be treated with not bad care. Below are a few most common errors caused by supplying incorrect arguments:
- If row_index_num is less than one, the HLOOKUP function returns the #VALUE! error.
- If row_index_num is greater than the number of rows in table_array, #REF! error is returned.
- If you lot search with guess friction match and your lookup_value is smaller than the smallest value in the first row of table_array, the #Due north/A error is returned.
Well, this is how to use HLOOKUP in Excel. Hopefully you volition notice this information helpful. I give thanks you for reading and hope to run into you lot on our blog next week!
Download practise workbook
Excel HLOOKUP formula examples
You may as well be interested in
Source: https://www.ablebits.com/office-addins-blog/2016/12/14/excel-hlookup-formula-examples/
Posted by: guzmaneity1946.blogspot.com
0 Response to "How To Use Hlookup In Excel 2016"
Post a Comment