How to Use VLOOKUP and XLOOKUP in Excel: Step-by-Step

TL;DR: To use Excel lookup functions, pick VLOOKUP for compatibility with older Excel (2019 and earlier), and XLOOKUP for new work on Excel 365 or Excel 2021. VLOOKUP needs four arguments and must search the leftmost column. XLOOKUP needs three, can look left, and defaults to exact match. The most common error — #N/A — is almost always trailing spaces or a text-vs-number mismatch in the lookup column.

If you know how to use Excel lookup functions, you can stop scrolling through long sheets to find a single value. Think of VLOOKUP as a dictionary — you give it a word, it gives you the meaning, but the words must be in the first column of the book. XLOOKUP is the newer, smarter cousin. It does the same thing more flexibly, and it is the default lookup function in Excel 365 and Excel 2021. In 24 years of training in Singapore, I have watched these two functions trip up beginners and rescue overworked finance teams in equal measure. This guide shows how to use each, when to pick one over the other, and how to fix the errors you will hit on the way.

The article walks through VLOOKUP first, because it is still the formula most teams meet in shared workbooks. Then XLOOKUP. Then a side-by-side comparison. Then what to do on older Excel versions where XLOOKUP simply does not exist (a real problem in many Singapore corporates). And finally, a troubleshooting section for the errors that surface in your first hour.

Hands-on with a trainer in the room. Our Excel Training course in Singapore covers VLOOKUP, XLOOKUP, PivotTables and dashboards — WSQ-funded, SkillsFuture-eligible, ACTA-certified trainers.

What an Excel lookup actually does (and why you need one)

Picture this. Your boss sends you a list of 200 order IDs at 5:45 pm. She wants the customer name and the price for each one, by 6:30. Manual scrolling is not an option. This is the moment Excel lookup functions earn their keep.

A lookup takes a value you already know and uses it to find one you do not. You hand it the known value — an employee ID, an order number, a product code — point it at a table that holds both that known value and the answer you want, and it returns the answer. No scrolling. No copying values one by one.

Excel ships with several lookup functions:

• VLOOKUP — vertical lookup. Searches down the leftmost column of a range and returns a value from a column to the right.
• XLOOKUP — the newer, more flexible replacement for VLOOKUP. Searches in any direction, returns from any direction.
• HLOOKUP — horizontal lookup. Same idea as VLOOKUP, but across rows instead of down columns. Rarely needed in practice.
• INDEX MATCH — a two-function combination that does what XLOOKUP does, written the long way. Useful when you are stuck on an older Excel version.

The two you need to master are VLOOKUP and XLOOKUP. The rest you will only meet in legacy workbooks. If you are still building muscle memory with everyday formulas, the essential Excel formulas for finance professionals guide pairs well with this one.

VLOOKUP syntax explained in one minute

VLOOKUP takes four arguments. Three are required, one is optional.

=VLOOKUP( what_you_search_for , the_table , answer_column_number , [exact_or_approx] )

In Microsoft’s own naming, those four slots are lookup_value, then table_array, then col_index_num, then the optional range_lookup. Here is what each one means in plain English:

• lookup_value — the value you are searching for. Could be a piece of text, a numeric value, a calendar date, or a cell reference such as E3.
• table_array — the range of cells that contains both the column you are searching and the column you want to return. The lookup column must be the leftmost column of this range.
• col_index_num — the column number within the table_array that holds your answer. The leftmost column is 1, the next is 2, and so on.
• range_lookup — FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match. For almost every workplace task, you want FALSE.

That is the whole function. Honestly. The reason VLOOKUP trips people up is not the syntax — it is forgetting that the lookup column must be on the left, and counting col_index_num from the start of the table_array, not from column A of the sheet. I have been training this for 24 years and I still see that mistake on day one.

How to use VLOOKUP in Excel — 4 steps with an example

Suppose you have a list of staff in cells A2 to C50. Employee ID in column A, name in column B, department in column C. In cell F2 you have an employee ID, and you want the department to appear in cell G2.

Step 1 — The lookup value. In cell G2, type =VLOOKUP( and then click cell F2 (or type F2). The formula now reads =VLOOKUP(F2,. This tells VLOOKUP what to search for.

Step 2 — The table array. Select the full range that contains the lookup column and the return column. Here that is A2:C50. The formula now reads =VLOOKUP(F2, A2:C50,. If you plan to copy the formula down the column, lock the range with dollar signs: $A$2:$C$50. Think of the dollar sign as super glue — it pins the reference so it does not slide when you copy.

Step 3 — The column index number. Department is in column C. Counting from the start of the table_array (A is 1, B is 2, C is 3), the column index is 3. The formula now reads =VLOOKUP(F2, $A$2:$C$50, 3,.

Step 4 — Exact or approximate match. Type FALSE (or 0) to demand an exact match. The completed formula is:

=VLOOKUP(F2, $A$2:$C$50, 3, FALSE)

Press Enter. The department for that employee ID appears in G2. Copy the formula down to handle the rest of the list. Do try it on your own data — that is when the function clicks.

Use TRUE (approximate match) only when your lookup column is sorted in ascending order and you genuinely want the nearest value below. Looking up a tax bracket by income, or a shipping band by weight. For everything else, stick with FALSE. Microsoft’s official VLOOKUP reference documents every argument if you want to dig deeper.

How to use VLOOKUP from another sheet or workbook

Lookups earn their keep when the table you are searching lives somewhere else. A master employee list on a separate sheet. A price list in a different workbook. A reference table maintained by another team. Managers can be quite demanding about this — they want the answer pulled cleanly from the source, not retyped.

From another sheet in the same workbook. Put the sheet name and an exclamation mark in front of the range:

=VLOOKUP(F2, MasterList!$A$2:$C$500, 3, FALSE)

If the sheet name contains spaces or special characters, wrap it in single quotes: 'Master List'!$A$2:$C$500.

From another workbook. Open both files, write the formula by clicking across, and Excel inserts the file name in square brackets for you:

=VLOOKUP(F2, [PriceList.xlsx]Prices!$A$2:$C$500, 3, FALSE)

When the source workbook is closed, Excel rewrites the reference with the full file path. The formula keeps working as long as the file stays where it is. If the file moves or gets renamed, the lookup breaks. You will see #REF! or a prompt to update links.

Tip — use a named range. If the same lookup table is referenced from many places, define a name for it (Formulas tab → Define Name). Then your formula reads =VLOOKUP(F2, EmployeeList, 3, FALSE). Easier to read. Easier to audit. Cross-sheet lookups are a building block for advanced Excel data analysis, where reference tables and transaction tables almost always live on separate sheets.

XLOOKUP syntax explained in one minute

XLOOKUP takes up to six arguments. Three are required.

=XLOOKUP( what_you_search_for , the_search_column , the_return_column , [fallback_text] , [match_type] , [scan_direction] )

In Microsoft’s documentation those slots are named lookup_value, lookup_array, return_array, then optional if_not_found, match_mode, and search_mode. What each one does:

• lookup_value — the value you are searching for. Same as VLOOKUP.
• lookup_array — the single column (or row) you are searching in. Just the lookup column, not the whole table.
• return_array — the single column (or row) you want the answer from. Can be on either side of the lookup_array — that is the big change.
• if_not_found — what to return if no match is found. Replaces the old IFERROR(VLOOKUP(...), "Not found") wrapper.
• match_mode — 0 for exact match (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
• search_mode — 1 search top to bottom (the default), -1 bottom to top, 2 binary ascending, -2 binary descending.

The big change from VLOOKUP: XLOOKUP takes the lookup column and the return column as two separate ranges, not one table with a column number. So no more counting columns. The return column can sit to the left of the lookup column. And XLOOKUP defaults to exact match, which removes the most common VLOOKUP mistake (forgetting the FALSE). How good is that?

How to use XLOOKUP in Excel — step-by-step with an example

Same scenario. Employee ID in column A, name in column B, department in column C. Employee ID in F2, answer goes in G2.

In cell G2, type:

=XLOOKUP(F2, $A$2:$A$50, $C$2:$C$50)

That is it. Three arguments. No column counting, no FALSE. Press Enter and the department appears.

To also handle the case where the ID does not exist, add if_not_found:

=XLOOKUP(F2, $A$2:$A$50, $C$2:$C$50, "Not on file")

When the lookup fails, the cell shows Not on file instead of #N/A. One argument. No IFERROR wrapper needed. Fantastic.

Left lookup. XLOOKUP can return values from a column to the left of the lookup column — something VLOOKUP simply cannot do without restructuring the table. If you have department in column A and employee ID in column C, this works:

=XLOOKUP(F2, $C$2:$C$50, $A$2:$A$50)

Two values back at once. If you want both name and department in one go, point return_array at both columns:

=XLOOKUP(F2, $A$2:$A$50, $B$2:$C$50)

The result spills into two adjacent cells (G2 and H2) using Excel’s dynamic-array behaviour. If you also need to slice that result list, our walkthrough on how to filter data in Excel is the natural next step.

VLOOKUP vs XLOOKUP — side-by-side comparison

If you only have time to read one section of this article, read this one. It tells you which function to reach for in each situation.

The short rule. If your workbook will only ever be opened in Excel 365 or Excel 2021, use XLOOKUP. If the file will be shared with anyone on Excel 2019 or earlier — and in Singapore enterprises and government agencies that is still common — keep using VLOOKUP, or use INDEX MATCH for left lookups.

Ready to build real-world Excel skills your manager will notice? Our Excel Training course covers VLOOKUP, XLOOKUP, PivotTables and dashboards with hands-on practice. SkillsFuture credits accepted.

When XLOOKUP isn’t available — Excel 2019 and earlier

XLOOKUP launched in Excel 365 in 2019 and was later added to Excel 2021 and Excel for the web. It does not exist in Excel 2019, Excel 2016, Excel 2013, or in any of the LTSC (Long-Term Servicing Channel) builds many corporates standardise on. Earlier editions of Excel do not have this new function. After 24 years training Singapore professionals, I can tell you this is still the single biggest “why does my formula say #NAME?” question in the classroom. The person built a beautiful XLOOKUP at home on Excel 365, sent the file to a colleague on the company-issued Excel 2019 LTSC laptop, and the formula simply died.

On those versions you have two options.

Option A — Stay with VLOOKUP. Use the 4-step approach above. For “if not found” behaviour, wrap it in IFERROR:

=IFERROR(VLOOKUP(F2, $A$2:$C$50, 3, FALSE), "Not on file")

This is the dominant pattern in legacy finance and HR workbooks across Singapore. It works in every Excel version from 2007 onwards. Boring, but reliable.

Option B — Use INDEX MATCH for left lookups. The one thing VLOOKUP cannot do — return a value from a column to the left of the lookup column — INDEX MATCH can. The pattern is:

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

So if department is in column A and employee ID is in column C, and you want department from an ID:

=INDEX($A$2:$A$50, MATCH(F2, $C$2:$C$50, 0))

MATCH finds the row number where the ID lives. INDEX returns the value from that row in the department column. The 0 in MATCH means exact match. INDEX MATCH is more verbose, but it works in every modern version of Excel. And it does not break if someone inserts a column.

HLOOKUP is the horizontal cousin of VLOOKUP — search across a row, return from another row. Useful when your data is laid out horizontally (months across the top, metrics down the side, for example), but rare in practice. Most teams transpose horizontal data into vertical layout and use VLOOKUP or XLOOKUP instead.

Common errors and how to fix them

Three errors account for almost every lookup problem in your first hour with these functions. I know it can feel intimidating when the cell goes red. Don’t worry — we will make sense of it.

#N/A — value not found. The lookup ran, but no match was found. Real causes, in order of frequency:

1. Trailing spaces in either the lookup value or the source data. "EMP001 " does not equal "EMP001". The screen will not show you that extra space, but Excel sees it. Wrap suspect references in TRIM() to test: =VLOOKUP(TRIM(F2), TRIM_RANGE, 3, FALSE).
2. Type mismatch. Numbers stored as text on one side, real numbers on the other. The cell may display EMP001 on both sides, but one is text and the other is a number formatted with a leading zero. Convert with VALUE(), or use Find and Replace to clean the data.
3. Capitalisation differences in the data. VLOOKUP and XLOOKUP are both case-insensitive for the comparison, so this is rarely the actual cause. But worth checking when the other two are ruled out.
4. You really do not have the value in the source. Sometimes the data is genuinely missing. Use the if_not_found argument in XLOOKUP, or IFERROR around VLOOKUP, to display a clear message instead of #N/A.

#REF! — column index out of range. Almost always a VLOOKUP problem. You asked for column 5 in a 4-column range. Or someone deleted a column from your table_array and the col_index_num is now pointing past the end. Fix the number, or move to XLOOKUP / INDEX MATCH where this error class does not exist.

Wrong column returned (no error, just wrong answer). Specific to VLOOKUP. You wrote =VLOOKUP(F2, $A$2:$C$50, 3, FALSE) last quarter. The formula has been working fine. Then someone inserts a column between B and C. Your “department” column is now column D, but the formula still reads column 3. You get the wrong column back with no error to warn you. Of course your boss will be unhappy.

This is the single strongest argument for moving new work to XLOOKUP — it references the return column directly, so inserting columns elsewhere in the sheet does not silently change the answer. If your lookups slow your workbook to a crawl, or you find yourself stitching together several reference tables, Power Query and Power Pivot handle that scale better than VLOOKUP chains.

Frequently asked questions

Is XLOOKUP better than VLOOKUP?

For new work in Excel 365 or Excel 2021, yes. XLOOKUP is exact-match by default, can look left, can return multiple columns in one formula, has a built-in “if not found” argument, and does not break when columns are inserted. The only case where VLOOKUP wins is compatibility. If your file will be opened on Excel 2019 or earlier — common in Singapore corporates and government agencies — XLOOKUP will not work there. Stay with VLOOKUP or INDEX MATCH.

Why is my VLOOKUP returning #N/A?

In four out of five cases, the cause is trailing spaces or a text-versus-number type mismatch in the lookup column. The cell looks correct on screen, but Excel sees two different values. Wrap the lookup value in TRIM() to test for spaces, and check that both sides are stored as the same data type (text or number). If everything matches and you still get #N/A, the value genuinely is not in the source table. Use IFERROR or the XLOOKUP if_not_found argument to handle that case cleanly.

Does XLOOKUP work in Excel 2019?

No. XLOOKUP is available in Excel 365 (Microsoft 365), Excel 2021, and Excel for the web. It was not added to Excel 2019, Excel 2016, or any earlier version. It is also not available in the Long-Term Servicing Channel (LTSC) builds that many large Singapore organisations standardise on. On those versions you will see a #NAME? error when you try to use it. Stay with VLOOKUP, or use INDEX MATCH for cases where you need a left lookup.

Can VLOOKUP look left?

No. VLOOKUP can only search the leftmost column of the table_array, and only return a value from a column to the right of it. If you need to search a column and return a value from somewhere to the left of it, you have three options. Restructure the table so the lookup column is on the left. Use INDEX MATCH. Or use XLOOKUP if you are on Excel 365 / 2021. XLOOKUP is the cleanest solution. INDEX MATCH is the one that works on every modern Excel version.

How do I VLOOKUP from another sheet?

Put the sheet name and an exclamation mark before the range inside the table_array argument. For example: =VLOOKUP(F2, MasterList!$A$2:$C$500, 3, FALSE). If the sheet name contains spaces, wrap it in single quotes: 'Master List'!$A$2:$C$500. The same idea extends to other workbooks — Excel adds square brackets around the filename. Lock the range with dollar signs if you plan to copy the formula down, so the reference does not slide as you copy.

When should I still use INDEX MATCH instead of XLOOKUP?

Two situations. First, when the workbook will be opened on Excel 2019 or earlier where XLOOKUP does not exist — INDEX MATCH works there, and gives you the left-lookup ability VLOOKUP cannot. Second, when you are maintaining an existing workbook built on INDEX MATCH already. Rewriting working formulas just to use the newer function is rarely worth the audit risk. For new work on Excel 365 or Excel 2021, XLOOKUP is the simpler choice.

That is everything I would teach in the first session of an Excel class on lookups. Try one of the formulas tonight on your own data — pick a list you already work with, define the question, and write the formula. The function clicks the moment you use it on something real. I hope you’ll like this — give it a try, and you will wonder how you managed without it. If you want hands-on practice with a trainer in the room, our WSQ-funded Excel training in Singapore is SkillsFuture-eligible.