XLOOKUP Function in Excel: Step-by-Step Guide for Beginners

XLOOKUP is the easiest modern lookup function for most Excel users because it finds a match in one range and returns a value from another range without forcing you to count columns. If you are a beginner, that matters: you can write clearer formulas, return values from the left or right, and show a friendly message instead of a raw error.

This guide walks through what XLOOKUP does, how the syntax works, and how to use it in realistic beginner examples. If you want the short reference version first, start with the XLOOKUP function page. If you are ready to understand how it works in real worksheets, keep going here.

What XLOOKUP Does and When to Use It

XLOOKUP searches for a value in one row or column, then returns the matching value from a second row or column. In plain English, it answers questions like:

• What is the price for this product ID?
• Which department belongs to this employee number?
• What grade matches this score?
• Which value should appear when this code is entered?

That makes it a direct replacement for many jobs people used to do with VLOOKUP, HLOOKUP, or an INDEX plus MATCH combination.

For beginners, the biggest advantages are simple:

• XLOOKUP defaults to exact match instead of approximate match.
• It can return values from the left or the right.
• It has a built-in if_not_found argument.
• You do not need to count a return column number the way you do with VLOOKUP.

If you have already struggled with the older lookup tools, compare this guide with The VLOOKUP Function in Excel: The Essential Guide and INDEX and MATCH Functions Together Again in Excel. XLOOKUP usually feels easier because the formula matches the way people think about the task.

XLOOKUP Syntax Explained for Beginners

The full syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Here is what each part means:

• lookup_value: the value you want Excel to find
• lookup_array: the row or column where Excel should search
• return_array: the row or column that contains the answer you want back
• if_not_found: optional text or value to show when no match exists
• match_mode: optional rule for exact, approximate, or wildcard matching
• search_mode: optional rule for search direction or binary search

You do not need all six arguments to get started. Most beginners only need the first three, and the fourth is useful when you want a clean message instead of #N/A.

Here is the simplest version:

=XLOOKUP(A2,$E$2:$E$10,$F$2:$F$10)

Excel looks for the value in A2 inside E2:E10, then returns the matching result from F2:F10.

Step-by-Step Example: Return an Exact Match

Imagine you have a small product table:

• Column E contains product IDs
• Column F contains product names
• Column G contains prices

In cell A2, you type a product ID and want Excel to return the price.

Use this formula:

=XLOOKUP(A2,$E$2:$E$10,$G$2:$G$10)

This is what happens:

1. Excel reads the product ID in A2.
2. It searches for that ID in E2:E10.
3. When it finds a match, it returns the price from the same row in G2:G10.

That is already simpler than VLOOKUP for many readers because you do not need a col_index_num. With VLOOKUP, the equivalent formula would require a table range and a counted column position. If someone inserts a new column later, the VLOOKUP version is more likely to break.

Step-by-Step Example: Look to the Left

One of the most useful XLOOKUP features is that it can return a value from the left side of the lookup column.

Suppose:

• Column F contains employee names
• Column G contains departments
• Column H contains employee IDs

If you enter an employee ID in A2 and want the employee name back, XLOOKUP can return the value from F, even though F is to the left of H.

=XLOOKUP(A2,$H$2:$H$20,$F$2:$F$20)

That is a common beginner win. Traditional VLOOKUP cannot search one column and return from a column to its left. In older workbooks, people often used INDEX and MATCH to work around that limitation. If you want to understand that older pattern, INDEX and MATCH Functions Together Again in Excel is still worth knowing. But if XLOOKUP is available in your version of Excel, it is usually the cleaner choice.

Step-by-Step Example: Show a Friendly "Not Found" Message

When a lookup value is missing, Excel often returns #N/A. That is technically correct, but it is not always friendly in a workbook other people will use.

XLOOKUP lets you handle that directly with the optional fourth argument:

=XLOOKUP(A2,$E$2:$E$10,$G$2:$G$10,"Not found")

If Excel cannot find the product ID from A2, the cell will show Not found instead of #N/A.

That matters in customer lists, inventory sheets, and simple dashboards because it gives the reader a clear answer instead of an error code. It is also one reason many users prefer XLOOKUP over older formulas that needed extra wrappers to make the result easier to read.

Step-by-Step Example: Use Approximate Match

XLOOKUP is not only for exact matches. You can also use it for breakpoints such as commission levels, tax bands, or grades.

Here is an example that returns the exact match or the next smaller value:

=XLOOKUP(B2,$J$2:$J$7,$K$2:$K$7,,-1)

In this case:

• B2 is the score or amount you are checking
• J2:J7 contains the sorted breakpoint values
• K2:K7 contains the result labels
• -1 tells Excel to find an exact match or the next smaller item

This is one of the few times you need to think carefully about match_mode. For a beginner, the safest habit is still to start with exact match and add approximate logic only when the worksheet really calls for it.

How XLOOKUP Compares with VLOOKUP, HLOOKUP, and INDEX MATCH

These functions solve related problems, but they do not feel the same when you build formulas.

XLOOKUP vs VLOOKUP

VLOOKUP is still common, and many older spreadsheets depend on it. The function works well when the lookup key is in the first column of the table and you are returning values to the right.

But XLOOKUP is easier to maintain because:

• the lookup range and return range are separate,
• exact match is the default,
• you can look left,
• you can add if_not_found directly.

If you often inherit older files, The VLOOKUP Function - Inside Out is a good companion piece. It explains why VLOOKUP works the way it does and why so many people accidentally get approximate matches.

XLOOKUP vs HLOOKUP

HLOOKUP exists for tables laid out horizontally instead of vertically. If your lookup values are in the top row and the answer is in a lower row, HLOOKUP can still do the job.

But XLOOKUP can work horizontally too, which means many readers no longer need to learn two separate lookup functions. For a traditional explanation, see HLOOKUP In Excel: Everything You Need to Know. For new workbooks, XLOOKUP is often the more straightforward place to start.

XLOOKUP vs INDEX MATCH

Before XLOOKUP, many advanced users preferred INDEX plus MATCH because it was more flexible than VLOOKUP. That is still true in some legacy situations, and the combination remains useful to understand.

However, for most beginner and intermediate cases, XLOOKUP compresses the same idea into one formula that is easier to read:

=INDEX($G$2:$G$10,MATCH(A2,$E$2:$E$10,0))

can often become:

=XLOOKUP(A2,$E$2:$E$10,$G$2:$G$10)

That is not just shorter. It is easier to explain, easier to audit, and easier to teach.

Common XLOOKUP Mistakes

Even a beginner-friendly function can still go wrong. These are the mistakes that cause most problems:

Searching one range and returning a different-sized range

The lookup_array and return_array should line up. If one range has 10 rows and the other has 9, the result will be unreliable or return an error.

Forgetting that hidden spaces still matter

If A2 looks like INV-1001 but the source data contains an extra trailing space, your formula may not match. This is one reason lookup formulas fail across imported data. If that problem sounds familiar, read VLOOKUP Not Working with Text Values: 6 Fixes You Need next.

Using approximate logic on unsorted breakpoint tables

When you use non-default match modes for approximate results, your source data needs to follow the correct order. If it does not, the returned result may be wrong even though the formula looks valid.

Assuming XLOOKUP exists in every Excel version

Microsoft documents XLOOKUP in current support materials for modern Excel environments, but availability can still depend on the version or license you are actually using. If the function is not recognized in your workbook, use VLOOKUP or INDEX with MATCH instead.

For the official syntax and current Microsoft guidance, see the Microsoft Support XLOOKUP documentation. For extra worked examples, Exceljet's XLOOKUP reference is also useful.

Should Beginners Learn XLOOKUP First?

Yes, in most cases.

If your version of Excel supports it, XLOOKUP is the first lookup function most beginners should learn because it is clearer than VLOOKUP, more flexible than HLOOKUP, and easier to read than INDEX MATCH.

That does not mean the older functions stopped mattering. Many real workbooks still rely on them, and you will still run into them in shared files, templates, and older courses. But if your goal is to solve real lookup tasks with the least friction, XLOOKUP is the best starting point.

Key Takeaways

• XLOOKUP finds a match in one range and returns a value from another.
• It is usually easier for beginners than VLOOKUP, HLOOKUP, or INDEX MATCH.
• It defaults to exact match, can look left, and supports a built-in Not found message.
• The safest beginner pattern is to master exact matches first, then learn approximate and wildcard options later.

If you want to keep building from here, use the XLOOKUP function reference, then compare it with VLOOKUP and INDEX and MATCH Functions Together Again in Excel. That sequence will give you the strongest foundation for lookup formulas in Excel.