|
COMPARE.LISTS Function
This function does a compare of a LookFor list with a LookIn list. The function is designed to efficiently help you reconcile two lists of items and find either the items that do match or the items that don’t match.
COMPARE.LISTS is capable of comparing 2 lists, each containing more than a million items, in a small number of seconds.
COMPARE.LISTS can return:
True/False or “”/”**” for each item in the LookFor list showing whether or not it can be found the LookIn list.
A count of the items in LookFor NOT FOUND in LookIn
A count of the items in LookFor FOUND in LookIn
A list of the items in LookFor NOT FOUND in LookIn
A list of the items in LookFor FOUND in LookIn
COMPARE.LISTS is a multi-threaded, non-volatile array function.
COMPARE.LISTS can handle whole-column references efficiently.
COMPARE.LISTS Syntax
COMPARE.LISTS (LookFor, LookIn, Output, Case_Sensitive)
The first two parameters are required. The function is designed to be entered either as a multi-row, single-column array formula or as a single-cell non-array formula.
LookFor (Required)
A single-column vertical range or array of data containing the items to be searched for in the LookIn list.
Empty cells in the LookFor list are ignored.
LookIn (Required)
A single-column vertical range or array of data containing the list of items to be searched for each item in the LookFor list.
Empty cells in the LookIn list are ignored.
If either of LookFor or LookIn refers to a single empty cell COMPARE.LISTS will return #Value.
Output (Optional: Default=3 if entered as a single cell formula, otherwise 1)
This option controls the type of output returned from COMPARE.LISTS.
Output=1: An array of True/False for each item in the LookFor list showing whether or not it can be found in the LookIn list.
When using Output=1 enter the COMPARE.LIST function as a multi-cell array formula alongside the LookFor list or as a dynamic array formula so that you get a corresponding True or False for each item in LookFor.
Output=2: An array of “” or “**” for each item in the LookFor list showing whether or not it can be found the LookIn list. “**” indicates that the item was not found.
When using Output=2 enter the COMPARE.LIST function as a multi-cell array formula alongside the LookFor list or as a dynamic array formula so that you get a “” or “**” for each item in LookFor.
Output=3: A Count and list of the items in LookFor that are NOT found in LookIn. The first output cell contains the count. To get only the count of non-matching items enter COMPARE.LISTS as a single-cell formula.
Output=4: A Count and list of the items in LookFor that ARE found in LookIn. The first output cell contains the count. To get only the count of matching items enter COMPARE.LISTS as a single-cell formula.
Output=5: A list of the items in LookFor that are NOT found in LookIn (No count).
Output=6: A list of the items in LookFor that ARE found in LookIn (No count).
Case_Sensitive (Optional: Default=False)
This option controls whether text will be compared case-sensitively or not. It can be True or False, and the default value is False so that by default lower-case text matches upper-case text.
|
