What does the Google Sheets XLOOKUP function do? It can extract a single or multiple values from the row in which the search key is found. By nesting XLOOKUP inside another XLOOKUP function, you can get the data at the intersection of a column with the first search key and the row with the second search key.

For an intro to XLOOKUP, please refer to the tutorial whose link is given below.

-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?

XLOOKUP can return a single row or column with the search key:

[ Ссылка ]
-------------------------------------
How to Use VLOOKUP in Google Sheets?

Use VLOOKUP to get a single value:

[ Ссылка ]
-------------------------------------
How to Use IFS in Google Sheets?

IFS allow multiple logical expressions:

[ Ссылка ]
-------------------------------------
How to Use IF in Google Sheets?

Use IF to compare one value with another:

[ Ссылка ]
-------------------------------------
How to Use IF with AND in Google Sheets?

Use IF with AND to Use Multiple Logical Expressions in IF:

[ Ссылка ]
-------------------------------------
How to Create a Pivot Table in Google Sheets?

Create a pivot table for calculation and in-depth data analysis:

[ Ссылка ]
-------------------------------------
Which one to Use? A Pivot Table, or COUNTBLANK Function, to Count Blank Cells in Google Sheets:

Gives steps to use a pivot table or COUNTBLANK to count blank cells:

[ Ссылка ]
-------------------------------------
How to Use a Pivot Table to Sum by Rows or Columns without using Formula in Google Sheets?

Sum numbers by rows and or columns easily, using a pivot table:

[ Ссылка ]
-------------------------------------
How to Create a Pivot Table to Get Maximum Value without Using Formula in Google Sheets?

A pivot table assists to get the maximum value in a data range without using a formula:

[ Ссылка ]
-------------------------------------

Here's the syntax of the XLOOKUP function formula:

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

Here's the syntax of the same function without the optional arguments:

=XLOOKUP(search_key, lookup_range, result_range)

To get the data at the intersection of the column with the first search key and the row with the second search key, nest another XLOOKUP function for the result_range argument:

=XLOOKUP(search_key, lookup_range, XLOOKUP(search_key, lookup_range, result_range))

The second XLOOKUP will search for the second search key horizontally and return its corresponding data to the first XLOOKUP. It will then match the first search key with the returned data and return the corresponding data.

Take a look at this video tutorial, which gives the step to nest the Google Sheets XLOOKUP functions inside another XLOOKUP to conduct a vertical and horizontal search and extract the data, with examples.

свернуть