HLOOKUP Function in Excel: Ultimate Guide

HLOOKUP stands for Horizontal Lookup. It searches for a value in the top row of a table and returns a value from the same column in a row you specify. Use HLOOKUP when your lookup values are arranged horizontally across the top row, rather than vertically down the first column.

---

HLOOKUP Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Argument	Required?	Description
lookup_value	Yes	The value you want to find in the top row.
table_array	Yes	The range containing the top lookup row and return rows.
row_index_num	Yes	The row number in table_array from which to return a value.
range_lookup	Optional	FALSE for exact match. TRUE for approximate match. If omitted, Excel assumes TRUE.

---

Important HLOOKUP Rules

Rule 1: HLOOKUP searches only in the top row

The value you are looking for must be in the first row of the selected range.

Example:

=HLOOKUP("Feb", B1:D4, 2, FALSE)

Excel looks for "Feb" in row 1 of B1:D4.

Rule 2: Row numbering starts at 1 inside the table range

If your range is:

B1:D4

then:

• Row 1 = B1:D1
• Row 2 = B2:D2
• Row 3 = B3:D3
• Row 4 = B4:D4

So:

=HLOOKUP("Feb", B1:D4, 2, FALSE)

returns a value from row 2 of the range.

Rule 3: Use FALSE for exact match in most cases

=HLOOKUP("Feb", B1:D4, 2, FALSE)

If you omit FALSE, Excel uses approximate match by default, which can produce unexpected results.

---

Exact Match vs Approximate Match

Exact Match: FALSE

Use exact match when you want to find the exact lookup value.

=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

Best for:

• Month names
• Product codes
• Department names
• Region names
• Column headers
• Specific dates

Example:

=HLOOKUP("Feb", B1:D3, 2, FALSE)

If Excel does not find the exact value, it returns:

#N/A

Approximate Match: TRUE

Use approximate match when you want Excel to find the closest match.

=HLOOKUP(lookup_value, table_array, row_index_num, TRUE)

Important:

• The top row must be sorted in ascending order from left to right.
• If an exact match is not found, Excel returns the largest value that is less than the lookup value.

Approximate match is useful for:

• Commission tiers
• Discount levels
• Tax brackets
• Grade boundaries
• Rate tables

---

Basic HLOOKUP Example

Assume this horizontal table:

	B	C	D
1	Jan	Feb	Mar
2	500	600	700
3	300	350	400

Formula:

=HLOOKUP("Feb", B1:D3, 2, FALSE)

Result:

600

Explanation:

• Look for "Feb"
• Search in the top row of B1:D3
• Return the value from row 2
• Use exact match

Return Row 3 Instead

=HLOOKUP("Feb", B1:D3, 3, FALSE)

Result:

350

---

Practical HLOOKUP Examples

Example 1: Lookup Sales by Month

	A	B	C	D
1	Metric	Jan	Feb	Mar
2	Sales	500	600	700
3	Costs	300	350	400
4	Profit	200	250	300

Formula to get February sales:

=HLOOKUP("Feb", A1:D4, 2, FALSE)

Result:

600

Formula to get March costs:

=HLOOKUP("Mar", A1:D4, 3, FALSE)

Result:

400

Example 2: Use a Cell Reference

If cell F1 contains:

Feb

Formula:

=HLOOKUP(F1, A1:D4, 2, FALSE)

Returns February sales.

Example 3: Lookup from Another Sheet

If your horizontal table is on Sheet2:

=HLOOKUP(F1, Sheet2!A1:D4, 2, FALSE)

If the sheet name has spaces:

=HLOOKUP(F1, 'Sales Data'!A1:D4, 2, FALSE)

Example 4: Approximate Match for Commission or Rates

Suppose you have this horizontal rate table:

	B	C	D	E
1	0	1000	5000	10000
2	0%	5%	10%	15%

Formula:

=HLOOKUP(7000, B1:E2, 2, TRUE)

Result:

10%

Why?

• 7000 is between 5000 and 10000.
• Excel finds the largest top-row value less than or equal to 7000.
• That value is 5000.
• It returns the corresponding rate: 10%.

Important:

The top row must be sorted ascending:

0, 1000, 5000, 10000

Example 5: Grade Lookup Using HLOOKUP

	B	C	D	E	F
1	0	60	70	80	90
2	F	D	C	B	A

Formula:

=HLOOKUP(85, B1:F2, 2, TRUE)

Result:

B

Because 85 is between 80 and 90.

Example 6: Dynamic Row Number Using MATCH

Instead of hard-coding the row number, use MATCH.

Assume this table:

	A	B	C	D
1	Metric	Jan	Feb	Mar
2	Sales	500	600	700
3	Costs	300	350	400
4	Profit	200	250	300

You want:

• Lookup month in cell F1
• Lookup metric name in cell G1

Formula:

=HLOOKUP(F1, A1:D4, MATCH(G1, A1:A4, 0), FALSE)

Example:

• F1 = Feb
• G1 = Profit

MATCH("Profit", A1:A4, 0) returns 4.

So the formula becomes:

=HLOOKUP("Feb", A1:D4, 4, FALSE)

Result:

250

Example 7: Two-Way Lookup with HLOOKUP and MATCH

HLOOKUP can be combined with MATCH to lookup both:

• Across the top row
• Down the side column

Assume:

	A	B	C	D
1	Metric	Jan	Feb	Mar
2	Sales	500	600	700
3	Costs	300	350	400
4	Profit	200	250	300

Inputs:

• F1 = Month, for example Feb
• G1 = Metric, for example Costs

Formula:

=HLOOKUP($F$1, $A$1:$D$4, MATCH($G$1, $A$1:$A$4, 0), FALSE)

If:

• F1 = Feb
• G1 = Costs

Result:

350

Example 8: Wildcard Lookup with HLOOKUP

HLOOKUP can use wildcards when using exact match.

Wildcard	Meaning
*	Matches any number of characters
?	Matches one character
~	Escapes a wildcard character

Example:

=HLOOKUP("*" & F1 & "*", A1:D4, 2, FALSE)

If F1 contains:

feb

Excel searches for:

*feb*

This can match values like:

Feb
February
Feb-2025

Note: HLOOKUP text matching is usually not case-sensitive.

Example 9: Return Multiple Rows in Microsoft 365

In Microsoft 365 or Excel with dynamic arrays, you can use an array constant:

=HLOOKUP(F1, A1:D4, {2,3,4}, FALSE)

This can return rows 2, 3, and 4 side by side.

Example:

=HLOOKUP("Feb", A1:D4, {2,3,4}, FALSE)

May return:

600    350    250

In older versions of Excel, you may need separate formulas for each row.

Example 10: Handle Missing Values with IFNA

=IFNA(HLOOKUP(F1, A1:D4, 2, FALSE), "Not Found")

If the lookup value is not found, this returns:

Not Found

Instead of:

#N/A

---

Common HLOOKUP Errors

#N/A

Meaning

Excel cannot find the lookup value.

Common Causes

• The value does not exist in the top row.
• You are using exact match, but there are extra spaces.
• Numbers are stored as text.
• The lookup row is not actually the first row of the range.
• Approximate match is being used with unsorted data.
• The lookup value is smaller than the first value when using approximate match.

Fixes

Check the top row manually.

Use TRIM:

=HLOOKUP(TRIM(F1), A1:D4, 2, FALSE)

Use IFNA:

=IFNA(HLOOKUP(F1, A1:D4, 2, FALSE), "Not Found")

#REF!

Meaning

The row index number is outside the table range.

Example

=HLOOKUP("Feb", B1:D3, 5, FALSE)

The range B1:D3 only has three rows, but the formula asks for row 5.

Fix

Reduce the row index number or expand the range.

Correct example:

=HLOOKUP("Feb", B1:D5, 5, FALSE)

#VALUE!

Meaning

Something is wrong with the formula arguments.

Common Causes

• row_index_num is less than 1.
• range_lookup is not TRUE, FALSE, 1, or 0.
• The lookup range is invalid.

Fix

Check the formula structure:

=HLOOKUP(F1, A1:D4, 2, FALSE)

#NAME?

Meaning

Excel does not recognize something in the formula.

Common Causes

• Misspelled function name:

=HLOOOKUP(F1, A1:D4, 2, FALSE)

• Misspelled sheet name or named range.

Fix

Check spelling of:

• Function name
• Sheet names
• Named ranges
• Table names

---

HLOOKUP Returns the Wrong Value

Common Causes

• You omitted FALSE, so Excel is using approximate match.
• The top row is not sorted when using approximate match.
• The lookup value matches the first duplicate.
• The lookup row is not the first row of the range.
• Numbers are stored as text.
• There are hidden spaces.

Fix

Usually, use exact match:

=HLOOKUP(F1, A1:D4, 2, FALSE)

---

HLOOKUP Limitations

Limitation 1: HLOOKUP only searches the top row

The lookup value must be in the first row of the selected range.

If your lookup value is in row 3 and you want to return row 1, HLOOKUP cannot do that directly.

Use:

• XLOOKUP
• INDEX/MATCH
• Rearranged data
• Power Query

Limitation 2: HLOOKUP cannot return values above the lookup row

HLOOKUP searches the top row and returns values from rows below it.

It cannot return values from rows above the top row of the table_array.

Limitation 3: HLOOKUP is not case-sensitive

These are treated as the same:

Feb
FEB
feb

If you need case-sensitive lookup, use INDEX and MATCH with EXACT.

Limitation 4: HLOOKUP returns only the first match

If the top row contains duplicates, HLOOKUP returns the first match it finds from left to right.

Example:

B	C	D
Feb	Feb	Mar
100	200	300

Formula:

=HLOOKUP("Feb", B1:D2, 2, FALSE)

Returns:

100

Limitation 5: Hard-coded row numbers can break

This formula uses row 2:

=HLOOKUP(F1, A1:D4, 2, FALSE)

If rows are inserted or deleted, the row number may become incorrect.

Better formula:

=HLOOKUP(F1, A1:D10, MATCH(G1, A1:A10, 0), FALSE)

Limitation 6: Approximate match can be dangerous

If you use:

=HLOOKUP(F1, A1:D4, 2)

or:

=HLOOKUP(F1, A1:D4, 2, TRUE)

the top row must be sorted ascending from left to right.

If it is not sorted, HLOOKUP may return an incorrect result without showing an error.

---

HLOOKUP vs VLOOKUP

Feature	HLOOKUP	VLOOKUP
Lookup direction	Horizontal	Vertical
Searches in	Top row	First column
Returns from	Row below	Column to the right
Best for	Wide tables	Tall tables
Exact match	Use FALSE	Use FALSE
Approximate match	Use TRUE	Use TRUE
Common use	Less common	Very common

Use HLOOKUP when your lookup values are across the top.

Use VLOOKUP when your lookup values are down the left side.

---

HLOOKUP vs XLOOKUP

If you have Microsoft 365, Excel 2021, or newer, XLOOKUP is usually better.

HLOOKUP Version

=HLOOKUP("Feb", B1:D3, 2, FALSE)

XLOOKUP Version

=XLOOKUP("Feb", B1:D1, B2:D2, "Not Found")

Why XLOOKUP is better

• Exact match is the default.
• Easier to read.
• Can look left, right, up, or down.
• Built-in error handling.
• No need to count row or column numbers.

---

HLOOKUP vs INDEX/MATCH

For older versions of Excel, INDEX/MATCH is often more flexible.

HLOOKUP Version

=HLOOKUP("Feb", B1:D3, 2, FALSE)

INDEX/MATCH Version

=INDEX(B2:D2, MATCH("Feb", B1:D1, 0))

This finds "Feb" in the top row and returns the matching value from row 2.

---

Best Practices for HLOOKUP

1. Use FALSE for exact match

=HLOOKUP(F1, A1:D4, 2, FALSE)

Do not omit the last argument unless you intentionally want approximate match.

2. Lock the lookup range

When copying formulas, use absolute references:

=HLOOKUP(F1, $A$1:$D$4, 2, FALSE)

Press F4 to add dollar signs.

3. Use MATCH instead of hard-coded row numbers

Instead of:

=HLOOKUP(F1, A1:D10, 3, FALSE)

use:

=HLOOKUP(F1, A1:D10, MATCH(G1, A1:A10, 0), FALSE)

4. Use approximate match only for sorted range tables

Approximate match is useful for:

• Tax brackets
• Commission rates
• Discount levels
• Grade boundaries

But the top row must be sorted ascending.

5. Handle errors cleanly

Use:

=IFNA(HLOOKUP(F1, A1:D4, 2, FALSE), "Not Found")

6. Clean your data

Check for:

• Extra spaces
• Numbers stored as text
• Dates stored as text
• Duplicate top-row values
• Misspelled labels
• Hidden characters

Useful functions:

=TRIM(A1)
=CLEAN(A1)
=VALUE(A1)

---

HLOOKUP Cheat Sheet

Exact match

=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

Approximate match

=HLOOKUP(lookup_value, table_array, row_index_num, TRUE)

Lookup from another sheet

=HLOOKUP(F1, Sheet2!A1:D4, 2, FALSE)

Handle #N/A

=IFNA(HLOOKUP(F1, A1:D4, 2, FALSE), "Not Found")

Dynamic row number

=HLOOKUP(F1, A1:D10, MATCH(G1, A1:A10, 0), FALSE)

Two-way lookup

=HLOOKUP($F$1, $A$1:$D$4, MATCH($G$1, $A$1:$A$4, 0), FALSE)

Modern replacement

=XLOOKUP(F1, B1:D1, B2:D2, "Not Found")

---

Summary

Use HLOOKUP when:

• Your lookup values are in the top row.
• You need to return values from rows below.
• Your data is arranged horizontally.

Most important rules:

• Use FALSE for exact match.
• The lookup value must be in the first row.
• Row numbering starts at 1 inside the selected range.
• Use TRUE only for sorted approximate-match tables.
• Use XLOOKUP or INDEX/MATCH when you need more flexibility.