How to Use XLOOKUP Function in Excel (Multiple Examples)

Hello everyone! Today i will show you how to use XLOOKUP formula in Microsoft Excel step by step.

XLOOKUP is new and powerful formula in Excel (Excel 365 and Excel 2021 and later). It can replace old formulas like VLOOKUP, HLOOKUP, and INDEX MATCH because it is very easy to use and never break when you insert new column!

📚 Related Tutorial: Want to compare VLOOKUP and XLOOKUP? Check out VLOOKUP vs XLOOKUP in Excel: When to Use Each!

🛠️ Formula Syntax & How It Works

The XLOOKUP formula look like this:

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

• lookup_value: What value you want to search (example: Employee ID in cell F2).
• lookup_array: Where column to search for that value (example: A2:A5).
• return_array: Where column to get the result back (example: B2:B5).
• [if_not_found] (Optional): Text to show if Excel cannot find value (example: "Not Found"). No more #N/A error!
• [match_mode] (Optional): 0 for exact match (default), -1 for exact or next smaller item, 1 for exact or next larger item, 2 for wildcard.
• [search_mode] (Optional): 1 search first to last (default), -1 search last to first.

📋 Sample Data Table (Follow Along)

To follow all examples below easily, create this simple table in your Excel sheet:

💡 Search Cell: We put our search target ID in Cell F2 = "E103".

📊 Simple Examples

Example 1: How to find one value (Basic Lookup)

We want to find the Name (Column B) for Employee ID "E103" in Cell F2:

=XLOOKUP(F2, A2:A5, B2:B5)

• Lookup Value (F2): "E103"
• Lookup Array (A2:A5): Column A (Emp ID list)
• Return Array (B2:B5): Column B (Name list)
• Result: Dara Sok

Example 2: How to lookup value to the left (Left Lookup)

Unlike old VLOOKUP, XLOOKUP can search right-to-left!

Suppose your table has Name in Column A and Emp ID in Column C:

Search ID in Cell F2 ("E103") in Column C (C2:C5) and get Name from Column A (A2:A5):

=XLOOKUP(F2, C2:C5, A2:A5)

• Result: Dara Sok (Works left to right or right to left without complex INDEX-MATCH!)

Example 3: How to show custom text when not found (No #N/A Error)

If search cell F2 contains "E999" (which does not exist in table):

=XLOOKUP(F2, A2:A5, B2:B5, "Not Found")

• Result: Not Found
• Note: If you leave if_not_found empty, Excel shows #N/A error. When you add "Not Found", it shows clean message!

Example 4: How to find and return multiple values (Multiple Columns)

Search ID in Cell F2 ("E103") in Column A (A2:A5) and bring back both Department (Column C) and Salary (Column D) together:

=XLOOKUP(F2, A2:A5, C2:D5)

• Result in Cell G2 (Department): Accounting
• Result in Cell H2 (Salary): $1,100
• Note: Excel automatically spills the answer into neighbor cells with only 1 single formula!

💡 Pro Tips for XLOOKUP Formula

1. Exact Match by Default: You don't need to type FALSE or 0 for exact match like VLOOKUP. XLOOKUP always do exact match automatically!
2. Never Break on Column Insert: If you insert or delete columns in your table, XLOOKUP updates cell references automatically and never breaks.
3. Search Last to First: Use search_mode = -1 to find latest entry or last transaction in your table fast!
4. Use Absolute References: Always use $ symbol (like $A$2:$A$5) when drag formula down to copy to other rows.

📚 Related Resources

• VLOOKUP vs XLOOKUP in Excel: Simple Guide & Comparison
• How to Use INDEX and MATCH in Excel
• How to Use IF Function in Excel
• How to Use SUMIF and SUMIFS Function in Excel
• Top 15 Basic Excel Formulas for Beginners