XLOOKUP function in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the XLOOKUP function do in Excel?

XLOOKUP searches a range or array for a value and returns a matching value from another range or array. It helps find data quickly without sorting.

beginner

What is the basic syntax of the XLOOKUP function?

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

beginner

What does the [if_not_found] argument do in XLOOKUP?

It tells Excel what to show if the lookup value is not found. For example, you can show "Not found" instead of an error.

intermediate

How does XLOOKUP differ from VLOOKUP?

XLOOKUP can look left or right, works with rows or columns, and does not require the lookup column to be sorted. VLOOKUP only looks right and needs sorted data for approximate matches.

intermediate

What does the [match_mode] argument control in XLOOKUP?

It controls how Excel matches the lookup value: exact match, exact or next smaller, exact or next larger, or wildcard match.

Which argument in XLOOKUP specifies where to find the value you want to return?

A lookup_value

B lookup_array

C if_not_found

D return_array

The return_array is where XLOOKUP looks to get the value to return after finding the lookup_value in the lookup_array.

What will XLOOKUP return if the lookup value is not found and [if_not_found] is not set?

A 0

B An error (#N/A)

C Empty cell

D The lookup value itself

If [if_not_found] is not provided, XLOOKUP returns the #N/A error when it cannot find the lookup value.

Which of these is NOT a valid match_mode option in XLOOKUP?

A Approximate match with sorting

B Exact or next smaller item

C Exact or next larger item

D Exact match

XLOOKUP does not require sorting and does not have an approximate match mode that depends on sorting like VLOOKUP.

Can XLOOKUP search from bottom to top?

A Yes, using search_mode argument

B No, it only searches top to bottom

C Only if data is sorted

D Only with wildcards

XLOOKUP’s search_mode argument lets you choose search direction, including bottom to top.

Which function can replace both VLOOKUP and HLOOKUP?

A INDEX

B MATCH

C XLOOKUP

D LOOKUP

XLOOKUP can look up values in any direction and replace both VLOOKUP and HLOOKUP.

Explain how to use XLOOKUP to find a price for a product in a list.

Think about what you want to find and where to find it.

Describe the advantages of XLOOKUP over VLOOKUP.

Focus on flexibility and error handling.