HLOOKUP in Excel: How to Find Values Across a Row

HLOOKUP searches across the top row of a table and returns a value from a row beneath it. Use it when your lookup values run horizontally, such as months across columns, and you want Excel to return a related result. You will need a worksheet containing the lookup row and the data to return, plus the row number of the result within that range.

What You’ll Need

An Excel worksheet with lookup values in the first row of a selected range.
Data in the rows below those values, arranged in matching columns.
The value to find, and the position of the row containing the answer.

Step-by-Step

Arrange the data horizontally. Put the values HLOOKUP should search in the top row of a continuous range. Place each related result in the same column underneath its corresponding lookup value. For example, put month names in B1:D1, sales totals in B2:D2, and sales targets in B3:D3. Avoid merged cells in the lookup row.
Choose the result cell. Click the blank cell where you want the answer to appear. In this example, you could use a cell outside the table, such as F2.
Start the formula. Type =HLOOKUP(. Excel may show a prompt describing the arguments. The function uses this syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
Enter the value to search for. Add the value itself, or select a cell containing it. For example, if E2 contains Feb, type =HLOOKUP(E2,. Text typed directly into a formula needs quotation marks, as in "Feb".
Select the entire table range. Include the top lookup row and every row that may supply an answer. For the example above, enter $B$1:$D$3. The top row of this range is where HLOOKUP searches; do not select only the answer row. Dollar signs make the range absolute so it stays fixed if you copy the formula.
Set the result row number. Count rows from the top of the selected range, not from the worksheet. In $B$1:$D$3, the month names are row 1, sales totals are row 2, and targets are row 3. To return the sales total, enter 2.
Choose the match type. Enter FALSE for an exact match in most everyday lookups. Enter TRUE, or omit this optional argument, for an approximate match. With approximate matching, the first row must be sorted in ascending order. For the example, finish with FALSE.
Complete and check the formula. Type the closing parenthesis and press Enter. The complete example is =HLOOKUP(E2,$B$1:$D$3,2,FALSE). If E2 contains Feb, and the matching sales total is in C2, the formula returns C2’s value.
Copy the formula if needed. Drag the fill handle or copy and paste it into other result cells. Confirm that the lookup cell changes as intended while the table range remains fixed. If required, adjust the lookup reference, such as changing E2 to E3.

Common Mistakes

Using the wrong orientation: HLOOKUP looks across the first row. If lookup values are in the first column, use VLOOKUP or a more flexible lookup function instead.
Counting the worksheet row: The row index is counted from the top of the selected range. If the range starts on worksheet row 10, its first row still has index 1.
Leaving out the match argument: Omitting the fourth argument defaults to approximate matching. Use FALSE for an exact lookup unless you specifically need approximate behavior and have sorted the lookup row.
Selecting a range that is too short: The range must include the lookup row and the result row. If it does not, you may see #REF! or get no useful result.
Using inconsistent values: A number stored as text may not match the same number stored as a numeric value. Extra spaces or slightly different text can also prevent an exact match.
Forgetting that HLOOKUP returns the first match: If the lookup value appears more than once across the top row, HLOOKUP returns the result from its first occurrence.

Tips

Use FALSE for names, labels, IDs, and other lookups that should match exactly.
Use cell references for both the lookup value and table range, so the formula is easier to reuse.
Lock a reusable table range with dollar signs, such as $B$1:$D$3. Leave the lookup reference unlocked when copying down if it should change by row.
For approximate matching, sort the first row from smallest to largest. HLOOKUP returns the value associated with the largest lookup value that does not exceed the search value.
If you need to return a result from a column to the left or want to choose lookup and return areas separately, consider XLOOKUP or INDEX with MATCH, depending on which functions your spreadsheet supports.

Troubleshooting

#N/A: With FALSE, Excel could not find an exact match. Check spelling, spaces, number-versus-text formats, and whether the lookup value is actually in the range’s first row. To reveal a friendlier message, you can wrap the formula in IFNA, for example =IFNA(HLOOKUP(E2,$B$1:$D$3,2,FALSE),"Not found").
#REF!: The row index is larger than the number of rows in the selected range, or the referenced range was deleted. Make sure the range includes the requested result row and the row index is within its height.
#VALUE!: Check that the row index is a valid positive number and that the formula arguments are separated correctly for your regional settings. Some Excel configurations use semicolons rather than commas.
Unexpected approximate result: Set the fourth argument to FALSE for exact matching. If approximate matching is intended, sort the top row in ascending order and verify that the lookup value is within its range.
The formula appears as text: Change the cell format to General, then re-enter the formula. Check that it starts with = and does not have an apostrophe or leading space before it.
The answer does not update: Confirm that calculation is set to Automatic in Excel’s Formulas calculation options, and check that the source cell or range has not changed.

Frequently Asked Questions

What does HLOOKUP do?
It searches for a value across the first row of a range and returns a value from a specified row in the same column.

What is the difference between HLOOKUP and VLOOKUP?
HLOOKUP searches horizontally across a top row. VLOOKUP searches vertically down a first column.

Should I use TRUE or FALSE?
Use FALSE when you need an exact match. Use TRUE for approximate matching only when the first row is sorted in ascending order.

Can HLOOKUP return a value to the left of the match?
It returns a value from the same column as the match, so its return range must include that column. It cannot independently search one range and return from an unrelated column.

Why does HLOOKUP return the wrong row?
The row index may be counted from the worksheet instead of from the selected range. Count the top row of the range as 1, then count down to the desired result.

Can HLOOKUP search for part of a text value?
Yes. With exact matching, wildcard characters such as * for multiple characters and ? for one character can match text patterns. Use a tilde before a wildcard to search for it literally.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →