Google Sheets | XLOOKUP | Function | Compare Two Lists | How To | Example | Spreadsheet | Tutorial

2 месяца назад

110 Просмотров

A powerful Google Sheets function is XLOOKUP. It can be used, for example, to compare two lists and extract data, for instance, of items from the second list, corresponding to the matched item in the first list.

Let's consider an example: Assume there are two lists List A and List B, pertaining to bicycle and accessories. Say an item common to the two lists is Rechargeable Bicycle Light. Use XLOOKUP two compare the two lists and extract data relating to the said common item.

For more info on XLOOKUP, please review its exclusive tutorial How to Use XLOOKUP to Extract Multiple Values, whose link is given below.

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

Use XLOOKUP to extract values from more than one cell in a row:

https://youtu.be/oYsXgdzuAkg
*************************************

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

Use VLOOKUP to get a single value:

https://youtu.be/_yKn70cl-Mo
-------------------------------------
How to Create a Pivot Table in Google Sheets?

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

https://youtu.be/9mbW3hxs2Cw
-------------------------------------
How to Use Convert to Table in Google Sheets?

Convert a data range into a table easily:

https://youtu.be/hxsPL34EaRU
-------------------------------------
How to Use REDUCE in Google Sheets?

Use REDUCE to get the final value of intermediate calculations:

https://youtu.be/kpdanWUAxno
-------------------------------------
How to Use SCAN in Google Sheets?

It's easy to calculate running total, running count and the like with SCAN:

https://youtu.be/9Pj3_9fadfA
-------------------------------------
How to Use QUERY in Google Sheets?

QUERY helps, among others, to extract specific or all data from a range:

https://youtu.be/X36ILm2S_xI
-------------------------------------
How to Use FREQUENCY in Google Sheets?

FREQUENCY returns the frequency distribution of data:

https://youtu.be/mDKjYlqVHEk
-------------------------------------
How to Use COUNTIFS in Google Sheets?

Count values that fulfill one or more criteria:

https://youtu.be/urrL985PvRk
-------------------------------------

Here's the format of XLOOKUP Function Formula:

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

Let's consider an example:

Assume there are two lists. The first list spans the range A3 to A8. The second list spans the range C3 to C8.

The objective of using XLOOKUP is to compare the two lists and extract data from the second list of items that are also present in the first list.

Assume the following values for the function arguments:

search_key=A3=Rechargeable Bicycle Light

lookup_range=A3:A8

result_range=C3:D8

missing_value="---". This is the value that will be returned for items that are
present in only one list.

Here is the XLOOKUP function formula with the above values:

=XLOOKUP(A3, A3:A8, C3:D8, "---")

Assume the above formula returns this data:

Rechargeable Bicycle Light 336

Copy the formula down the cells to compare and extract other values.

Checkout this video tutorial, which gives the steps to use the Google Sheets
XLOOKUP function to compare two lists and extract data, with examples.

Тэги: