XLOOKUP function in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the XLOOKUP function do in Google Sheets?

XLOOKUP searches a range or array for a value and returns a matching value from another range or array. It helps find data quickly, like looking up a friend's phone number by their name.

beginner

What is the basic syntax of the XLOOKUP function?

The syntax is =XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]). You give it what to find, where to look, and what to return.

intermediate

What happens if XLOOKUP does not find the search key and you provide the if_not_found argument?

XLOOKUP will return the value you put in if_not_found instead of an error. For example, you can show "Not found" instead of an error message.

intermediate

How can you use XLOOKUP to find the closest match instead of an exact match?

Use the match_mode argument and set it to -1 for next smaller item or 1 for next larger item. This helps when exact matches might not exist.

intermediate

Why is XLOOKUP better than VLOOKUP or HLOOKUP?

XLOOKUP is more flexible: it can look left or right, up or down, and doesn’t require sorted data. It also handles missing values better and is easier to read.

What is the first argument in the XLOOKUP function?

A The range to return values from

B The value to return if not found

C The range to search in

D The value to search for

The first argument is the search key, the value you want to find.

If XLOOKUP does not find a match and no if_not_found is given, what happens?

A It returns zero

B It returns the first value in the return range

C It returns an error

D It returns blank

Without if_not_found, XLOOKUP returns an error if no match is found.

Which argument lets you choose approximate matching in XLOOKUP?

A if_not_found

B match_mode

C search_mode

D return_range

match_mode controls exact or approximate matching.

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 VLOOKUP

The search_mode argument can make XLOOKUP search from bottom to top.

Which of these is NOT a benefit of XLOOKUP over VLOOKUP?

A Requires sorted data

B Can look left or right

C Handles missing values better

D Supports approximate matches

XLOOKUP does NOT require sorted data, unlike VLOOKUP in some cases.

Explain how to use XLOOKUP to find a price of a product by its name in a list.

Think about what you want to find, where to look, and what to get back.

Describe how XLOOKUP handles cases when the search value is not found.

Consider what happens if you don’t tell XLOOKUP what to do when no match exists.