Google Sheets | XLOOKUP | Function | Extract Multiple Values | Example | Spreadsheet | Tutorial

The Google Sheets XLOOKUP function is a powerful and versatile function. Its many arguments gives the flexibility to construct a formula that would help extract data that meets your needs. Because of some limitations of VLOOKUP or HLOOKUP functions, you may want to give the XLOOKUP a try.

VLOOKUP and HLOOKUP, however, could fit the bill, for tasks that does not require the width and breadth of XLOOKUP. The links to VLOOKUP and HLOOKUP tutorials are given below.

-------------------------------------
How to Use VLOOKUP in Google Sheets?

Use VLOOKUP to extract a single value to the right of the search key:

-------------------------------------
How to Use HLOOKUP in Google Sheets?

Use HLOOKUP for horizontal lookup of search key and extract a single value to its right:

----------------------------------------
How to Sum Investment Amount by Quarter?

Sum data of investments, profits, revenues, etc., by quarter:

-------------------------------------
How to Sum Investment Amount by Month?

The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by month:

-------------------------------------
How to Sum Investment Amount by Week?

The step-by-step tutorial to sum investment amount (or profits, revenues, expenses, orders, etc.) by
week:

-------------------------------------
How to Get Number of Weeks between Dates in Google Sheets?

Here is the link to the step-by-step video tutorial on getting the number of weeks between dates and also the number of fractional days:

-------------------------------------
How to Use ROUND, ROUNDUP, and ROUNDDOWN in Google Sheets?

Get to know more about these three functions with this step-by-step video tutorial:

-------------------------------------
How To Use LCM in Google Sheets?

This is the link to the step-by-step video tutorial on using the LCM function to get the least common multiple of one or more numbers:

-------------------------------------

Here are the key advantages of XLOOKUP function:

XLOOKUP can return multiple values from a row or column. In contrast, VLOOKUP returns a single value.

XLOOKUP can return data that is to the left or right of the search key.

XLOOKUP can search from first to last cell of a row or column or from last to first.

XLOOKUP can search for an exact, approximate, or wildcard match. The default is exact.

XLOOKUP allows to use a text string to replace the default N/A error, which is returned if the function does not find a match.

This is the XLOOKUP function formula.

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Start the formula with an equal-to symbol.

XLOOKUP is the name of the function.

search_key is the data to search.

lookup_range is the range that might have the search key.

result_range is the range from which to extract data corresponding the cell with the search key.

missing_value is optional, and is the text string to replace the N/A error.

match_mode is the matching method. Its value is 0, for exact match; 1 for exact match or value greater than the search key; -1 for exact match or value lesser than the search key; 2 for wildcard match.

search_mode is the search method. Its value is 1 to search from first cell to last cell; -1 to search from last cell to first cell; 2 for binary search with data sorted in ascending order; -2 for binary search with data sorted in descending order.

Here are examples of the XLOOKUP function formula.

Example 1

=XLOOKUP(38512,A3:A8,A3:F8)

Here 38512 is the hard-coded search key. A3:A8 is the lookup range,
and A3:F8 is the result range.

Example 2

=XLOOKUP(H3,A3:A8,A3:F8)

In this example, H3 is the address of the cell with the search key. The function searches for this search key in the lookup range A3 to A8 and, if found, returns the corresponding data in the result range A3 to F8.

Review this video tutorial, which gives the steps to use the Google Sheets XLOOKUP function with examples.