True or false: microsoft excel can use headers & footers, just like microsoft word.
Show
The HLOOKUP function in Microsoft Excel looks for a value horizontally, across a row, to find an exact match, or the closest match. Then, it returns a value from another row, in same column where it found the value. HLOOKUP is similar to VLOOKUP, which is used for vertical lookups Video: HLOOKUP FunctionThe Excel HLOOKUP function can find an exact match in the lookup row, or it can find the closest match (approximate match). This short video tutorial shows both types of HLOOKUP:
When to Use HLOOKUPUse the HLOOKUP function when you need to:
Note: If you need to look for a value vertically, down a column, use Excel functions VLOOKUP or XLOOKUP instead. How to Use HLOOKUPEach function in Excel has a syntax -- the list of arguments the function needs, the order for those arguments, and whether each argument is required or optional. When creating a formula, Excel automatically shows a function's syntax after you type its name, and the opening bracket. HLOOKUP SyntaxThe HLOOKUP function has the following syntax, with 3 required arguments, and 1 optional argument:
NOTE: Arguments in square brackets are optional Required ArgumentsHere are the 3 required arguments for HLOOKUP, in the order they need to be entered: 1. lookup_value: What value should HLOOKUP look for, in the first row of the lookup range (table_array). This argument can be a value that you type into the formula, or a cell reference, for a cell that contains the lookup value. 2. table_array: Where is the lookup table that has the lookup values in the first row? This can be a range reference, or a range name, or a table name, with 2 or more columns. 3. row_index_num: Within the lookup table (table_array), which row has the values that HLOOKUP should return? This number can be different from the worksheet row number, if the lookup table does not start in row 1 on the worksheet. Optional ArgumentHere is the optional argument for HLOOKUP, and its order in the function's syntax: 4. [range_lookup]: Should HLOOKUP find an exact match or an approximate match? There are three options for entering this argument:
HLOOKUP WarningsThe HLOOKUP function can be slow, especially when looking for an exact match for a text string, in an unsorted table. For better results, try these suggestions:
HLOOKUP Exact MatchThe HLOOKUP function looks for a value in the top row of the lookup table. In this example, we'll find the sales total for a selected region. We want an exact match for the Region name, so the following settings are used:
The Excel formula in cell C7, in the screenshot below, is: =HLOOKUP(B7,C2:F3,2,FALSE) If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A HLOOKUP Approximate MatchUsually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. In this example, rates change at the start of each quarter, and those dates are entered as column headings. With HLOOKUP set for an approximate match, you can find the rate that was in effect for any date. In this example:
The formula in cell D5 is: =HLOOKUP(C5,C2:F3,2,TRUE) If the exact date is not found in the top row of a table, the HLOOKUP formula returns the rate for the next largest date that is less than lookup_value. The lookup value in this example is March 15th. That date is not in the date row (topmost row), so the value for January 1st (0.25) is returned. HLOOKUP with 2 CriteriaIn the previous examples, the lookup table had 2 rows:
In those HLOOKUP formulas, the row index number (2) was typed into the formula as the 3rd argument. For example:
Calculate the Row Index NumberIn some lookup tables, there might be multiple rows below the heading row. Instead of typing a row index number in the HLOOKUP function's arguments, you can use another function to calculate the correct row number, instead of typing it into the formula. In this product sales quantity lookup table:
Find Correct Row for Selected ItemIf we always wanted the chair quantity, its row number (3) could be typed into the HLOOKUP formula:
In this case though, we want to find the correct row index number, based on the Item name in cell C7. To do that, use the MATCH function to find the selected item's position in cells B2:B4.
Here is the formula in cell D7, where the HLOOKUP result is based on 2 criteria, with MATCH in the 3rd argument:
Tip: To learn more about the MATCH function, and see other examples of how to use it, go to the INDEX and MATCH page. HLOOKUP and COUNTIFAs mentioned in the HLOOKUP Warnings section (above), the HLOOKUP function can be slow, especially if you're looking for an exact match for a text string, in an unsorted table. This example uses these methods for making HLOOKUP work faster:
Here is the lookup table, with this HLOOKUP / COUNTIF formula in cell C5:
How the HLOOKUP and COUNTIF Formula Works1) First, the COUNTIF function counts the number of times that "Central" is found in cells C2:F2
2) Next, the IF function checks the result of the COUNTIF function 3) Then, the IF function returns its result:
HLOOKUP ErrorsOccasionally, an HLOOKUP formula returns an error value, instead of the result that you expected. Here are some of the error values, and what they mean: #VALUE!If the row index number is less than 1, HLOOKUP returns the #VALUE! error value. You might get that error if the row_index_number argument refers to a cell on the worksheet, and that cell is empty, or contains a zero. #REF!If the row index number is greater than the number of rows in the lookup table, HLOOKUP returns the #REF! error value. You might get that error if rows were deleted from the lookup table, and the row_index_number argument was not updated. #N/AIf a match for the lookup value is not found, the #N/A error value is returned. You might get that error if there is a typo in the lookup value, or if some values are real dates or numbers, and the other values are text dates or numbers. See the example in the next section - Problem: Match Dates or Numbers. HLOOKUP with WildcardsIn addition to looking for specific text values, you can also use wildcards with the HLOOKUP function. The following wildcard characters in Excel represent unknown characters, before, between, or after, other characters
Two Asterisk WildcardsIn the example shown below, there are two asterisk wildcards in the formula, before and after the reference to cell B5:
The formula finds the first product name that contains the letter, or string of letters, typed in cell B52, and returns that product's sales amount Asterisks in Lookup Value CellAnother option is to put wildcards in the reference cell that has the lookup value. For example, type i?p in cell B5, the the formula returns the sales amount for the Cookies product.
Problem: Match Dates or NumbersIn this example, the HLOOKUP function has a problem matching dates. Even though the dates are in the lookup table heading row, Excel returns an error, because it can't match the dates.
See why this problem occurs, and how to make a simple change to the HLOOKUP formula, to fix the problem Lookup Table With DatesIn this example, there is a lookup table, with
This list is formatted as an Excel table, and it is named RatesLU. HLOOKUP Formula With DatesBelow the lookup table, there are 2 cells with HLOOKUP formulas, to find the Target (row 2), and the Bonus% (row 3) for the date entered in cell B8. The formulas use a structured table reference for the lookup table: RatesLU[#All]
Tip: You can learn more about structured table references on the Microsoft site. HLOOKUP Formula ErrorsEven though the date in cell B8 looks the same as the date in cell D3, both HLOOKUP formulas show an #N/A error, because Excel could not find the lookup value in the heading row. HLOOKUP Problem with DatesIn this example, there is a problem with date matching, because:
Excel treats number dates and text dates as different values, even if they look the same. Table Headings Are Always TextOriginally, the dates in row 3 were entered as real dates (numbers). However, when the lookup table was formatted as a named Excel table, they automatically changed to text.
Fix HLOOKUP Problem with DatesThe HLOOKUP formula needs a small change, so Excel can see the heading dates (text) as real dates (number), and find a match for the lookup date (number). In each formula, type two minus signs (double unary) in front of the lookup table reference:
How It Works
Other HLOOKUP ProblemsIf this solution did not fix your HLOOKUP problem, try the suggestions in the Troubleshoot the VLOOKUP formula section, on the VLOOKUP page. For example,
On the VLOOKUP page, you'll see how to fix those problems, using functions or macros. These two functions are similar, and these troubleshooting tips might help solve your HLOOKUP problems too. Download the Sample FileTo see the formulas used in these examples, download the HLOOKUP function sample workbook. The zipped Excel file is in xlsx file format, and does not contain any macros. More Functions TutorialsCompare Lookup Functions VLOOKUP Function INDEX and MATCH Functions Count Functions INDIRECT Function What are headers used for in Excel?You can use a header to include the same information at the top of every printed page or a footer to include information at the bottom of every page. You can enter your own headers or footers, insert built-in ones, or insert specific elements such as pictures or page numbers.
Does Excel need headers for a table?Creating header rows improves the navigation of your document, which refers to how easily you can move across an area. If you have header rows, you can easily scan your spreadsheet for a specific header. This might help you find a dataset quickly.
|