XLOOKUP function in Excel - Step-by-Step Guide

Introduction

XLOOKUP helps you find information in a table quickly. It looks for a value you want and returns matching data from another column. This saves time compared to searching manually.

When you want to find a product price by entering its name.

When you need to get a student's grade by typing their ID number.

When you want to match employee names to their phone numbers.

When you want to replace older lookup formulas with a simpler one.

When you want to find data even if it is not sorted.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for typing

Step 2: Type

- the selected cell

Formula input starts

💡 Start typing =XLOOKUP(

Step 3: Enter the lookup value

- inside the parentheses after =XLOOKUP(

The first argument is set

💡 This is the value you want to find, like a product name or ID

Step 4: Type a comma and select the lookup array

- inside the formula after the lookup value

The second argument is set

💡 This is the column or range where Excel looks for the value

Step 5: Type a comma and select the return array

- inside the formula after the lookup array

The third argument is set

💡 This is the column or range where Excel gets the matching result

Step 6: Close the parentheses and press Enter

- at the end of the formula

The formula runs and shows the matching result in the cell

Before vs After

Before

You have a list of product names in column A and prices in column B. You want to find the price of 'Apple'.

After

In a cell, you type =XLOOKUP("Apple", A:A, B:B) and it shows the price of Apple from column B.

Settings Reference

match_mode

📍 4th argument in XLOOKUP formula

Controls how Excel finds the lookup value

Default: 0

search_mode

📍 5th argument in XLOOKUP formula

Controls the direction and method of search

Default: 1

if_not_found

📍 optional 4th argument in XLOOKUP formula

Value to return if lookup value is not found

Default: Error if not found

Common Mistakes

Using XLOOKUP with unsorted data but setting search_mode to binary search.

Binary search requires sorted data and can give wrong results if data is not sorted.

Use the default search_mode (1) for unsorted data.

Not closing the parentheses in the formula.

Excel shows an error and does not calculate the formula.

Always close the formula with a closing parenthesis ) before pressing Enter.

Selecting wrong ranges for lookup_array and return_array with different sizes.

XLOOKUP requires both ranges to have the same number of rows or columns.

Make sure both lookup_array and return_array cover the same number of cells.

Summary

XLOOKUP finds a value in one column and returns a matching value from another column.

It works with unsorted data and can replace older lookup formulas.

Remember to match the sizes of lookup and return ranges and close the formula properly.

Practice

1. What does the XLOOKUP function do in Excel?

easy

A. It creates a chart from data.

B. It adds up all numbers in a column.

C. It finds a value in a range and returns a corresponding value from another range.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of XLOOKUP

   XLOOKUP searches for a value in one range and returns a matching value from another range.

2. Step 2: Compare with other options

   The other options describe creating charts, adding up numbers, or sorting data, which are different Excel functions unrelated to XLOOKUP.

3. Final Answer:

   It finds a value in a range and returns a corresponding value from another range. -> Option C

4. Quick Check:

   XLOOKUP finds and returns matching values [OK]

Hint: XLOOKUP matches a value and returns related data [OK]

Common Mistakes:

• Confusing XLOOKUP with SUM or SORT functions
• Thinking XLOOKUP creates charts
• Assuming XLOOKUP only works with numbers

2. Which of these is the correct syntax for the XLOOKUP function?

easy

A. XLOOKUP(return_array, lookup_value, lookup_array)

B. XLOOKUP(lookup_value, return_array, lookup_array)

C. XLOOKUP(lookup_array, lookup_value, return_array)

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

Solution

1. Step 1: Recall XLOOKUP syntax

   The correct order is lookup_value, lookup_array, return_array, then optional arguments.

2. Step 2: Check each option

   Only XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) follows this correct order and includes optional parameters correctly.

3. Final Answer:

   XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) -> Option D

4. Quick Check:

   Correct syntax order = XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) [OK]

Hint: Remember: lookup_value, lookup_array, return_array order [OK]

Common Mistakes:

• Swapping lookup_array and return_array
• Omitting required arguments
• Using wrong argument order

3. Given the table below, what will =XLOOKUP(102, A2:A5, B2:B5, "Not found") return?

ID	Name
101	Alice
102	Bob
103	Charlie
104	Diana

medium

A. Alice

B. Bob

C. Not found

D. Charlie

Solution

1. Step 1: Identify lookup_value and lookup_array

   Lookup value is 102, lookup array is A2:A5 containing IDs 101, 102, 103, 104.

2. Step 2: Find matching value and return corresponding name

   102 matches second row, so return value from B2:B5 second row is 'Bob'.

3. Final Answer:

   Bob -> Option B

4. Quick Check:

   Lookup 102 returns Bob [OK]

Hint: Match lookup value in first column, return from second [OK]

Common Mistakes:

• Returning wrong row's value
• Confusing lookup and return arrays
• Expecting 'Not found' when value exists

4. What is wrong with this formula? =XLOOKUP(105, A2:A5, B2:B5) given the same table as above.

medium

A. It will return an error because 105 is not found and no if_not_found argument is given.

B. The lookup_array and return_array are reversed.

C. The formula syntax is missing parentheses.

D. It will return the last value in B2:B5 by default.

Solution

1. Step 1: Check if lookup_value exists in lookup_array

   105 is not in A2:A5 (101-104), so no match found.

2. Step 2: Understand default behavior without if_not_found

   Without the optional if_not_found argument, XLOOKUP returns #N/A error if no match.

3. Final Answer:

   It will return an error because 105 is not found and no if_not_found argument is given. -> Option A

4. Quick Check:

   No match without if_not_found causes error [OK]

Hint: Add if_not_found to avoid errors when no match [OK]

Common Mistakes:

• Assuming it returns last value by default
• Thinking syntax is incorrect
• Swapping lookup and return arrays

5. You want to find the price of a product by its code in column A and return the price from column C. If the product code is not found, you want to show "Price not available". Which formula correctly does this using XLOOKUP?

hard

A. =XLOOKUP(E2, A2:A10, C2:C10, "Price not available")

B. =XLOOKUP(A2:A10, E2, C2:C10, "Price not available")

C. =XLOOKUP(E2, C2:C10, A2:A10, "Price not available")

D. =XLOOKUP(E2, A2:A10, C2:C10)

Solution

1. Step 1: Identify lookup_value and arrays

   Lookup value is in E2 (product code), lookup array is A2:A10 (product codes), return array is C2:C10 (prices).

2. Step 2: Check for if_not_found argument

   =XLOOKUP(E2, A2:A10, C2:C10, "Price not available") includes "Price not available" for missing codes, fulfilling the requirement.

3. Final Answer:

   =XLOOKUP(E2, A2:A10, C2:C10, "Price not available") -> Option A

4. Quick Check:

   Correct lookup and return arrays with if_not_found [OK]

Hint: Use if_not_found to show message when no match [OK]

Common Mistakes:

• Swapping lookup and return arrays
• Omitting if_not_found argument
• Using wrong cell references