VLOOKUP, HLOOKUP, XLOOKUP
This is the full function-reference lesson on lookups: a side-by-side comparison of VLOOKUP, HLOOKUP, and XLOOKUP, their exact syntax, arguments, and edge cases. Where the intro lesson built intuition, this one is your go-to reference when you're actually writing formulas and need to remember argument order, error handling, and which function fits which layout.
Why this skill matters professionally
Interviews for admin, analyst, and finance-support roles frequently include a live Excel test that asks you to look up a value across sheets. Knowing the conceptual idea of a lookup isn't enough in that moment — you need fluent recall of exact syntax, what each argument does, and how to handle errors under time pressure. This reference-style mastery is also what separates someone who can maintain a legacy VLOOKUP-heavy workbook from someone who has to ask a coworker every time.
Learning objectives
- Write a working VLOOKUP with exact match.
- Replace it with XLOOKUP and understand why it's better.
Background concepts
Three functions, one family
VLOOKUP, HLOOKUP, and XLOOKUP all do the same fundamental job — match a value and return a related value — but differ in direction, flexibility, and error handling.
Vertical vs. horizontal
VLOOKUP searches down a column (vertical data, the vast majority of real spreadsheets). HLOOKUP searches across a row (horizontal data, rare — usually a dated column layout like Jan/Feb/Mar across the top).
Column index vs. array reference
VLOOKUP and HLOOKUP require you to count columns/rows to specify what to return. XLOOKUP eliminates counting by letting you point directly at the return range, which is both faster to write and far more resilient to inserted or deleted columns.
Error behavior differences
VLOOKUP and HLOOKUP return #N/A on no match with no built-in handling. XLOOKUP has a dedicated fourth argument for a custom not-found message, and a fifth argument for match mode (exact, approximate, wildcard).
Step-by-step
1. VLOOKUP full syntax
Four arguments, the last one critical.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value the value to search for
table_array the full table, key column must be leftmost
col_index_num which column to return, counting from 1 = leftmost
range_lookup FALSE = exact match (use this almost always)
TRUE or omitted = approximate match (sorted data only)2. VLOOKUP example
Returning a department name from an employee table.
Table (A:C): EmpID | Name | Department
=VLOOKUP("E1042", A2:C500, 3, FALSE) -> returns the Department3. HLOOKUP full syntax
Mirrors VLOOKUP but across rows.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Used when categories run across columns and data runs down rows below them, e.g. monthly budgets laid out Jan, Feb, Mar... across row 1.
4. XLOOKUP full syntax
Six arguments, only the first three are required.
=XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found], [match_mode], [search_mode])
lookup_value the value to search for
lookup_array the single column/row to search
return_array the single column/row to return from (same length)
if_not_found text/value to show instead of #N/A
match_mode 0=exact (default), -1=exact or next smaller, 1=exact or next larger, 2=wildcard
search_mode 1=first to last (default), -1=last to first, 2/-2=binary search5. XLOOKUP example with error handling built in
No IFERROR wrapper needed.
=XLOOKUP(A2, Employees[EmpID], Employees[Department], "Not on file")
6. Two-way lookup comparison
Both functions can look up on two dimensions, but the approach differs.
VLOOKUP two-way (needs a helper MATCH for the column): =VLOOKUP(A2, Table, MATCH(B2, HeaderRow, 0), FALSE) XLOOKUP two-way (nest two XLOOKUPs, no helper column needed): =XLOOKUP(A2, RowKeys, XLOOKUP(B2, HeaderRow, DataRange))
7. Choosing the right one for the layout
Ask what direction your data runs, whether you're on a modern Excel version, and whether columns might be reordered later.
- Data runs down, columns are stable, older Excel version: VLOOKUP
- Data runs across (rare): HLOOKUP
- Modern Excel (365/2021+), any layout, want resilience: XLOOKUP
8. Migrating a workbook from VLOOKUP to XLOOKUP
Find & Replace won't safely convert argument order — do it manually or with a small helper column, testing each converted formula against its original result before deleting the old one.
Real-world workplace examples
Finance monthly budget grid
A finance team stores budget categories across columns (Jan–Dec) and uses HLOOKUP to pull a specific month's figure into a summary report.
Legacy inventory system
An older Excel inventory tool still uses VLOOKUP throughout because it was built for Excel 2010 users who don't have XLOOKUP available.
Modern client-facing dashboard
A consultancy standardizes on XLOOKUP in all new deliverables so that inserting a column in the source table never breaks a client's report.
Two-way rate card
An operations analyst builds a shipping rate calculator where rows are origin zones and columns are weight brackets, using nested XLOOKUP to find the intersecting rate.
Practical scenarios
Auditing an inherited workbook
A new hire receives a 40-tab workbook riddled with VLOOKUPs that break every time someone adds a column. Rather than rewriting everything (risky under deadline), they document which formulas are fragile, add XLOOKUP versions in new tabs going forward, and gradually retire the VLOOKUP-based ones during scheduled maintenance rather than a risky big-bang rewrite.
Choosing match_mode for a commission tier lookup
A payroll analyst needs to find which commission tier a sales rep falls into based on total sales, where tiers are defined by minimum thresholds (0, 10000, 25000, 50000). Using match_mode -1 in XLOOKUP finds the exact tier or next smaller threshold, replicating VLOOKUP's approximate-match behavior but with full control and without requiring the data to be manually sorted.
Common mistakes beginners make
Confusing row_index and col_index between HLOOKUP and VLOOKUP
Because the two functions look nearly identical, people plug a column number into HLOOKUP's row argument. Remember: VLOOKUP counts columns, HLOOKUP counts rows.
Forgetting XLOOKUP requires equal-length arrays
lookup_array and return_array must be the same size, or XLOOKUP throws a #VALUE! error. Double-check both ranges start and end on the same rows.
Using match_mode 2 (wildcard) unintentionally
If lookup values contain literal asterisks or question marks, wildcard mode misinterprets them as pattern characters. Use match_mode 0 unless you specifically need partial matching.
Assuming XLOOKUP is available everywhere
Sharing an XLOOKUP-based file with someone on Excel 2016 causes #NAME? errors. Check your audience's Excel version before standardizing on it for shared files.
Hardcoding col_index_num instead of using MATCH
If someone inserts a column into the source table, a hardcoded column number now points at the wrong data with no error raised. Use MATCH for the column number, or switch to XLOOKUP entirely.
Best practices
- Default to XLOOKUP for new work when your audience's Excel version supports it.
- Keep VLOOKUP skills sharp since most legacy files still use it.
- Always confirm lookup_array and return_array are the same length in XLOOKUP.
- Use match_mode -1 or 1 in XLOOKUP instead of VLOOKUP's approximate-match TRUE for bracket/tier lookups.
- Document which Excel version a shared file targets if it uses XLOOKUP.
- Replace hardcoded column numbers with MATCH() when a table's structure might change.
- Use the if_not_found argument in XLOOKUP instead of wrapping in IFERROR — it's cleaner and self-documenting.
Professional tips
- XLOOKUP's search_mode -1 lets you search from the bottom up — useful for finding the most recent transaction matching an ID in a chronological log.
- Binary search modes (2/-2) in XLOOKUP are faster on huge sorted datasets but return wrong results on unsorted data — only use them when you're certain the data is sorted.
- Combine XLOOKUP with FILTER for 'return all matches' scenarios that a single lookup can't handle (lookups return only one row).
- Use Name Box or Ctrl+F3 to manage named ranges so lookup formulas stay readable as tables grow.
- In VLOOKUP, pressing F4 after selecting the table_array locks it with $ signs in one keystroke.
Practice exercises
Build all three
Given one dataset, write a VLOOKUP, an HLOOKUP (on a transposed copy), and an XLOOKUP that all return the same value, and compare the formulas side by side.
Break the column reference
Insert a new column into your VLOOKUP's source table and watch the result go wrong. Then fix it using MATCH() instead of a hardcoded index, and separately with an equivalent XLOOKUP that never broke.
Tiered lookup
Build a commission tier table and use XLOOKUP with match_mode -1 to find the correct tier for various sales totals.
Two-way nested XLOOKUP
Build a small grid (rows = regions, columns = quarters) and write a nested XLOOKUP that returns the value at the intersection of a chosen region and quarter.
Review questions
What's the core structural difference between VLOOKUP and XLOOKUP?
VLOOKUP requires the key to be the leftmost column and returns a value by counting columns; XLOOKUP lets you point directly at separate lookup and return ranges in any position.
When would you actually use HLOOKUP?
When your categories are laid out across a row instead of down a column, such as months across the top of a budget sheet.
What does match_mode -1 do in XLOOKUP?
Finds an exact match, or if none exists, the next smallest value — useful for tier and bracket lookups.
Why might a shared file with XLOOKUP fail for a colleague?
XLOOKUP only works in Excel 2021, Microsoft 365, or newer; older versions show a #NAME? error.
What's a safer alternative to hardcoding col_index_num in VLOOKUP?
Use MATCH() to calculate the column number dynamically, or switch to XLOOKUP which doesn't need column counting at all.
Key takeaways
- VLOOKUP: vertical data, key must be leftmost, counts columns to return a value.
- HLOOKUP: same idea but horizontal — rare in practice.
- XLOOKUP: points directly at lookup/return ranges, handles errors natively, supports flexible match modes.
- Always use exact match unless you deliberately need bracket/tier logic.
- match_mode -1/1 in XLOOKUP replicate and improve on VLOOKUP's approximate match.
- Check Excel version compatibility before standardizing a shared file on XLOOKUP.
- Use MATCH() to future-proof VLOOKUP column references against table changes.
Frequently asked questions
Is HLOOKUP ever actually necessary in modern workbooks?
Rarely — most people either transpose the data or use XLOOKUP with an array oriented in either direction, but it's still worth knowing for legacy horizontal layouts.
Can XLOOKUP replace HLOOKUP too?
Yes, XLOOKUP works on rows exactly the same way it works on columns — the lookup_array and return_array can be horizontal.
What error does XLOOKUP give for mismatched array sizes?
#VALUE!, indicating lookup_array and return_array don't have the same number of cells.
Does XLOOKUP work with wildcards?
Yes, set match_mode to 2 to enable wildcard characters like * and ? in the lookup_value.
Can I nest a MATCH inside VLOOKUP to look up in two dimensions?
Yes — use MATCH to calculate col_index_num dynamically based on a header lookup, giving VLOOKUP two-way lookup capability.
Which is faster on large datasets?
XLOOKUP with binary search_mode on pre-sorted data is fastest; otherwise the difference is negligible for typical business datasets under a few hundred thousand rows.
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced