VLOOKUP and HLOOKUP in Excel are the two formulas that turn a frustrating, manual spreadsheet grind into something that practically runs itself. Picture this: your manager drops a 600-row product list on your desk and asks you to match every item to its price from a separate catalog sheet. Before you know these formulas, that job takes an hour. After you know them, it takes about thirty seconds. That is not an exaggeration.

This guide will walk you through both functions from scratch — syntax, step-by-step examples, error fixes, and all. Whether you are pulling employee salaries from a database, matching product codes to prices, or grading student scores automatically, these two formulas do the heavy lifting for you.

By the end of this lesson you will know exactly how to write both formulas, when to use each one, and how to fix the errors that trip up almost every beginner.

What Are Lookup Functions and Why Do They Matter?

Here is the thing most spreadsheet beginners do not realise early enough. A huge amount of Excel work comes down to one question: given this value, what related information can you find? What is the price of this product? What is the salary of this employee? What grade does this score earn?

Lookup functions answer exactly that kind of question. You give Excel a value to search for, tell it where to look, and it brings back whatever you need from the same row or column. No scrolling, no copy-pasting, no human error.

The Problem They Solve

Before lookup formulas, people handled this stuff manually. They would search with Ctrl+F, copy a value, paste it somewhere else, move on to the next row, repeat five hundred times. That is not just slow — it is a recipe for mistakes that are genuinely hard to spot later.

VLOOKUP and HLOOKUP make the connection between two tables dynamic. Change something in your source data and the formula updates automatically. That alone saves people hours every week.

VLOOKUP vs HLOOKUP at a Glance

The difference between the two is simply the direction your data flows.

How to Use the Excel VLOOKUP Formula

VLOOKUP Syntax — All 4 Arguments Explained

The Excel VLOOKUP formula follows this structure:

Here is what each argument means:

1. lookup_value — This is the value you are searching for. It could be a cell reference like A2, a number, or a text string like “B204”.
2. table_array — The range of cells containing your data. The first column of this range must contain the values you are searching through.
3. col_index_num — The column number within your table_array that holds the value you want to return. Column 1 is the leftmost column of the table_array, not column A of the spreadsheet.
4. range_lookup — Enter FALSE for an exact match. Enter TRUE for approximate match. If you leave this blank, Excel defaults to TRUE — which can cause unexpected results.

Step-by-Step: Writing Your First VLOOKUP

Say your product catalog lives on Sheet2 — column A has product codes, column B has prices. On Sheet1, you want to type a product code into cell A2 and have the matching price appear in B2 automatically.

Your finished formula should look like this:

VLOOKUP Exact Match — Using FALSE in range_lookup

Trust me on this — use FALSE almost every time. The VLOOKUP exact match setting means Excel will only return a result when it finds a value that matches perfectly. If there is no match, you get an error, which sounds annoying but is actually useful. An error tells you something is wrong. A wrong answer looks fine on the surface and causes real problems later.

Use exact match whenever you are searching by IDs, codes, names, or any value that needs to be precisely right.

VLOOKUP Approximate Match — When TRUE Makes Sense

Approximate match has a specific, limited use case. It is designed for tiered lookups where you want to find the highest threshold that does not exceed your search value. Commission tables are the classic example — if sales between 0 and 9,999 earn 5%, and sales between 10,000 and 19,999 earn 10%, you can set up a table with those thresholds and use TRUE to return the right rate.

One condition: your table must be sorted in ascending order or the results will be completely unreliable.

HLOOKUP Excel Tutorial — Searching Across Rows

HLOOKUP Syntax Breakdown

The HLOOKUP formula is the mirror image of VLOOKUP. Same logic, different axis:

1. lookup_value — The value you are searching for in the first row of your table.
2. table_array — Your data range, where the first row holds the values to search through.
3. row_index_num — The row number within your table_array from which to return a value.
4. range_lookup — FALSE for exact match, TRUE for approximate match.

Step-by-Step: Writing Your First HLOOKUP

You have a grading rubric where row 1 contains score thresholds (0, 41, 61, 81) and row 2 contains grade letters (F, C, B, A). A student’s score sits in cell B2 and you want the grade to appear in C2.

Your formula will look something like this:

Real Example: Grading Rubric with HLOOKUP

You’ve probably seen teachers spend ages manually assigning grades from a marking sheet. With HLOOKUP, a teacher sets up the rubric once at the top of the spreadsheet, writes the formula once, and copies it down for all 30 students. Change the rubric thresholds and all 30 grades update instantly. It is the kind of thing that makes people look like spreadsheet wizards without doing anything particularly complicated.

VLOOKUP and HLOOKUP in Excel — Side-by-Side Comparison

Same Data, Two Orientations

Think of VLOOKUP and HLOOKUP as the same knife used for two different cuts. The logic is identical — you are matching a value and returning related information. The only question is whether your data runs top to bottom or left to right.

• Data in rows, headers in columns across the top — use VLOOKUP
• Data in columns, labels in rows down the left — use HLOOKUP
• Not sure — check whether you would search the first column or the first row

Decision Rule: When to Use Which

In day-to-day work, VLOOKUP wins by a wide margin. Most databases, exported reports, and tables are set up vertically. HLOOKUP comes into its own for horizontal summary tables, rubrics, and dashboards where categories run left to right. If you are building a new spreadsheet from scratch and you have a choice, go vertical — VLOOKUP is more widely supported and easier to troubleshoot.

VLOOKUP Across Multiple Sheets

Referencing a Table on Another Sheet

This is where VLOOKUP starts doing serious work. Rather than keeping all your data in one sheet — which gets messy fast — you can have a clean lookup table on one sheet and your working data on another. The formula is almost identical. You just include the sheet name followed by an exclamation mark before the range:

This searches column A on Sheet2, finds the match, and returns the value from column C of that same row.

Locking the Table Array with Absolute References ($)

Here is something that catches a lot of people out. When you copy a VLOOKUP formula down a column of cells, Excel shifts the table_array reference by default. That means by the time you get to row 10, your formula is searching a completely wrong part of the spreadsheet.

The fix is absolute references. Add dollar signs to lock your range in place:

No matter how far you copy the formula, the table_array stays exactly where you put it.

VLOOKUP Error Fix — Diagnosing and Solving Common Issues

Errors are not failures — they are messages. Each one tells you something different, and once you know the language, fixing them becomes straightforward.

#N/A Error — Causes and Fixes

The #N/A error means Excel searched through your table and could not find a match. Common causes include:

• Extra spaces — the value looks identical on screen but has a hidden space at the start or end. Fix this with =TRIM(A2)
• Data type mismatch — one column stores numbers as text and the other stores actual numbers
• A genuine missing value — the item simply is not in the lookup table

Start by checking for hidden spaces. That is the culprit more often than you would think.

#VALUE! Error — What Went Wrong

A #VALUE! error usually points to a problem with the col_index_num argument — either it is less than 1, or you have accidentally passed an array where Excel expected a single value. Double-check that argument first.

#REF! Error — Column Index Out of Range

A #REF! error means your col_index_num is higher than the number of columns in your table_array. If your table is three columns wide and you asked for column 5, Excel has nowhere to go. Count your columns carefully and adjust the index number.

Using IFERROR to Clean Up Error Displays

Nobody wants their carefully built spreadsheet showing a wall of red errors to other users. Wrap your VLOOKUP in IFERROR to display a clean, friendly message instead:

Now instead of an ugly error, the cell shows “Not found” — or whatever message makes sense in context. This is especially valuable in shared workbooks where colleagues will be entering data that might not always exist in the lookup table.

For more on combining formulas with logical functions like this, check out the IF Function in Excel lesson on XplorExcel.com.

VLOOKUP Limitations You Should Know

Why VLOOKUP Can Only Look Right

Here is the thing about VLOOKUP that surprises most people the first time they hit it. The function can only return values from columns to the right of the lookup column. If your lookup column is column D but the data you need is in column B, VLOOKUP simply cannot do it. That is a hard limitation baked into the function.

The Column Index Problem When Tables Change

There is another issue that is less obvious but arguably more dangerous. If someone inserts or deletes a column inside your table_array, your col_index_num becomes wrong. And because Excel does not throw an error — it just returns a different column’s data — you might not notice for days or weeks. It is a silent failure, and it has caused real problems in real workplaces.

When to Consider INDEX-MATCH or XLOOKUP Instead

INDEX-MATCH solves both of those problems. It can look in any direction, and it references columns by name rather than position, so insertions do not break it. XLOOKUP, available in Excel 365 and Excel 2021, goes even further — simpler syntax, no column counting, and the ability to return entire arrays.

That said, VLOOKUP and HLOOKUP in Excel remain standard in huge numbers of workplaces, especially those running older Excel versions. Learning them is not a waste of time — it is the foundation that makes everything else easier to pick up.

The INDEX and MATCH lesson on XplorExcel.com is the natural next step once you are comfortable here.

Practice Exercises

Key Takeaways

• VLOOKUP searches down a column. HLOOKUP searches across a row. The choice depends on how your data is laid out.
• Use FALSE for exact match in almost every real-world case.
• Always lock your table_array with absolute references (F4) before copying a formula.
• Wrap formulas in IFERROR to keep shared spreadsheets clean and user-friendly.
• VLOOKUP cannot look to the left. For that, INDEX-MATCH is the answer.
• Both functions are still used everywhere and worth knowing thoroughly, even as newer tools like XLOOKUP exist.

Frequently Asked Questions

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches the first column of a table and returns data from a column to the right. HLOOKUP searches the first row and returns data from a row below. Both use the same logic — the difference is purely the direction your data runs.

Why does my VLOOKUP return the wrong value?

Nine times out of ten it is one of two things. Either you used TRUE instead of FALSE in the range_lookup argument, so Excel returned an approximate match when you wanted an exact one. Or you copied the formula without locking the table_array, so the range shifted. Check both of those before digging deeper.

Can VLOOKUP look to the left?

No, it cannot. VLOOKUP is hard-wired to return values from columns to the right of the lookup column. If you need to look left, use INDEX-MATCH — that combination has no directional restriction.

Is VLOOKUP still useful or should I use XLOOKUP?

VLOOKUP and HLOOKUP in Excel are still active in millions of real workbooks right now. If your workplace uses Excel 2016 or 2019, you are going to need VLOOKUP. Even if you are on Excel 365 where XLOOKUP is available, understanding VLOOKUP first makes the newer function easier to learn. Start here, then expand.