Ravindra BagaleCourses & study guides Track your progress

Excel HLOOKUP Function

By · Last updated:

Chala mitrano — hLOOKUP is VLOOKUP lying on its side. Keys across the top row, answer in a row below, and use FALSE for exact month names.

Fictional sample data for learning. Monthly sales grid (Surat textile / Leeds showroom). Mixed ₹ and £.

Purpose

Look up a value in the top row of a table and return a value from a row beneath it. Useful when months or regions sit across columns.

Return value

The value from the chosen row of the matching column, or #N/A if nothing matches (exact mode).

Syntax

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

Arguments

ArgumentRequired?Description
lookup_valueRequiredThe value to find in the first row of table_array (month name, region code, or a cell).
table_arrayRequiredThe table. Top row holds keys; return rows sit below.
row_index_numRequiredWhich row to return, counting from 1 at the top of table_array.
range_lookupOptionalFALSE = exact (preferred). TRUE = approximate; top row must be sorted ascending.

Version

Excel 2007 and later, Microsoft 365, LibreOffice Calc. For modern work prefer XLOOKUP on a vertical list.

How to use — Why → What → How

Why: Dashboards often put Jan–Dec across columns. You need “Sales for Mar” without scanning every header. HLOOKUP shines on wide month grids — Jan to Dec across the top — common in textile MIS sheets from Surat and in UK showroom packs from Leeds. Row index 2 often holds Sales; row 3 h...

What: HLOOKUP finds lookup_value in the first row of table_array and returns the cell from row row_index_num of that column. Read the arguments table carefully; each one changes the answer in a specific way.

How — numbered steps:

  1. Click the answer cell.
  2. Type =HLOOKUP( then the month or region to find.
  3. Select the wide table (keys on the top row). Lock with F4.
  4. Type the row number to return (2 = first data row under the headers).
  5. Type FALSE, close, Enter.
  6. Compare your result with the Answers sheet only after you attempt it.
  7. Write one sentence in your own words explaining when you would use this function at work.

Ravindra Bagale's Tip

Friends, wide month headers are the classic HLOOKUP case. If your data is tall instead, transpose or use XLOOKUP — do not fight the shape.

Ravindra Bagale's Tip

Lock the table with $ before filling across. Row index is a number — inserting a row above the return row breaks it.

Ravindra Bagale's Tip

Friends, after class open a blank sheet and rebuild one HLOOKUP example without looking. That memory test beats re-reading the page.

Download practice file: practice-hlookup.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Exact — March sales

Surat textile monthly grid. Get Mar sales.

MetricJanFebMarApr
Sales (₹)120000135000142000128000
Formula: =HLOOKUP("Mar", A1:E2, 2, FALSE)

Result: 142000

What it means: Mar is found in the top row; row 2 of that column is returned.

2) Missing month → #N/A

Typo: May instead of Mar.

MetricJanFebMar
Sales (₹)120000135000142000
Formula: =HLOOKUP("May", A1:D2, 2, FALSE)

Result: #N/A

What it means: No May header. Use IFNA for reports.

3) UK showroom — £ row

Same layout; Leeds showroom stores pounds on row 3.

MetricJanFebMar
Sales (₹)120000135000142000
Sales (£)11009801250
Formula: =HLOOKUP("Feb", A1:D3, 3, FALSE)

Result: 980

What it means: Row index 3 returns the £ line for February.

4) IFNA friendly text

Manager typed Aug on a Q1-only sheet.

MetricJanFebMar
Sales (₹)120000135000142000
Formula: =IFNA(HLOOKUP("Aug", A1:D2, 2, FALSE), "No month")

Result: No month

What it means: IFNA replaces #N/A with clean wording.

5) Target row under the same months

Row 2 = Sales, row 3 = Target. Pull Mar target.

MetricJanFebMar
Sales120000135000142000
Target125000130000140000
Formula: =HLOOKUP("Mar", A1:D3, 3, FALSE)

Result: 140000

What it means: Same key row; different row_index_num picks Target.

6) Classroom recap

Quick check before you close the HLOOKUP lesson.

MetricJanFebMarApr
Sales (₹)120000135000142000128000
Formula: =HLOOKUP("Mar", A1:E2, 2, FALSE)

Result: 142000

What it means: Same pattern as example 1 — rebuild it from memory on the Practice sheet, then check Answers.

About this data. Small demo rows for learning. Real sheets are longer; the same formula pattern scales when columns are clean.

Common errors and fixes

#N/A — Header not found with FALSE. Fix: Fix spelling / trailing spaces; or IFNA.
#REF! — row_index_num larger than rows in table_array. Fix: Count rows from the top of the selected table.
Wrong month value — Omitted FALSE (defaults to TRUE) on unsorted text headers. Fix: Always pass FALSE for month names.
#VALUE! — row_index_num < 1. Fix: Use 1, 2, 3… only.

Notes and tips

  • HLOOKUP only returns rows below the key row — never above.
  • For vertical lists, VLOOKUP / XLOOKUP are clearer; use HLOOKUP when the sheet is already wide.
  • Duplicate headers: first match wins.
  • HLOOKUP shines on wide month grids — Jan to Dec across the top — common in textile MIS sheets from Surat and in UK showroom packs from Leeds. Row index 2 often holds Sales; row 3 holds Target or a pound line. Exact FALSE is mandatory for month names because TRUE expects sorted ascending headers and text months are a poor fit. If your data is tall (months down column A), do not force HLOOKUP; use XLOOKUP or INDEX+MATCH on the vertical layout instead.
  • HLOOKUP is to rows what VLOOKUP is to columns. Same pitfalls: numeric index, insert/delete shifts, approximate default. Prefer XLOOKUP when you can reshape.
  • Fictional sample data only — practice amounts and names are made for learning.
  • After you finish the 10 tasks, re-read the errors table once; most class mistakes are listed there.
  • Keep ranges locked with absolute references when you copy formulas down a column of inputs.
  • Say the business question in plain words before you type the equals sign — it prevents wrong arguments.

In real work

HLOOKUP shines on wide month grids — Jan to Dec across the top — common in textile MIS sheets from Surat and in UK showroom packs from Leeds. Row index 2 often holds Sales; row 3 holds Target or a pound line. Exact FALSE is mandatory for month names because TRUE expects sorted ascending headers and text months are a poor fit. If your data is tall (months down column A), do not force HLOOKUP; use XLOOKUP or INDEX+MATCH on the vertical layout instead.

How it compares

HLOOKUP is to rows what VLOOKUP is to columns. Same pitfalls: numeric index, insert/delete shifts, approximate default. Prefer XLOOKUP when you can reshape.

Interview corner

When is HLOOKUP the right tool?
When keys already sit across the top row and you need a value from a row beneath.
What is the modern alternative?
XLOOKUP on a vertical list, or INDEX+MATCH.
Why is FALSE important for month labels?
Omitting it defaults to approximate match, which mis-reads unsorted text headers.

VLOOKUP, XLOOKUP, INDEX, MATCH

Practice tasks (10)

  1. Task 1. Return Mar sales (₹) from the Monthly sheet.
    Hint: keys are on row 1; sales on row 2.
  2. Task 2. Return Feb sales in £.
    Hint: £ line is a lower row — change row_index_num.
  3. Task 3. Look up Aug and show No month when missing.
    Hint: IFNA around HLOOKUP.
  4. Task 4. Return Mar Target (not Sales).
    Hint: Target is one row below Sales.
  5. Task 5. Lock the table and copy the formula across for Jan–Mar.
    Hint: F4 on table_array; lookup value can be a header cell.
  6. Task 6. Explain when you would prefer XLOOKUP instead of HLOOKUP.
    Hint: think about data shape and left/right returns.
  7. Task 7. Deliberately omit FALSE and write what happens on this sheet.
    Hint: compare side by side.
  8. Task 8. Return Jan sales with a cell reference as lookup_value (not typed text).
    Hint: point at the Jan header cell.
  9. Task 9. Fix a #REF! caused by row_index_num = 9 on a 3-row table.
    Hint: count real rows in the selection.
  10. Task 10. Bonus: return Apr sales if the column exists.
    Hint: extend table_array to include Apr.

Quick quiz

Q1. HLOOKUP searches which part of the table first?
Q2. For month names you should set range_lookup to:
Q3. row_index_num 1 returns:
Q4. HLOOKUP can return a row ABOVE the lookup row.
Q5. Best modern alternative on Microsoft 365?

Answers: Q1=Top row, Q2=FALSE, Q3=The key row itself, Q4=False, Q5=XLOOKUP (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: HLOOKUP
HLOOKUP revision card