Excel validate entries in list of lists
I have a master list of things (column A below), and I have a list of lists (column C below), where all sub-entries should exist in the master list. I would like to validate the list of lists (in column D). For example, the below screenshot shows a mock-up example of the data and the expected result in column D:
What formula could I use for column D?
Solution
Here is one way of accomplishing the desired output using XMATCH() + TEXTSPLIT() + ISNUMBER() + AND() funtcions:
• Formula used in cell D2
=AND(ISNUMBER(XMATCH(TEXTSPLIT([@[List of lists]],","),Table1[Master List of Things])))
• Or bit shorter using 1-ISNA() instead of ISNUMBER() which does the same operation here:
=AND(1-ISNA(XMATCH(TEXTSPLIT([@[List of lists]],","),Table1[Master List of Things])))
- Comparing two byte arrays in .NET
- Filter out rows of a 2d array if the row is not found in another 2d array
- How to retain the index of an array element after flattening and sorting into descending order using sort()
- Create json-encoded array from a PDO query result column
- JSON encode a column of a 2d array
- JSON encode the first column of a 2d array
- need to json_encode a PHP array in a particular way
- Using string path to delete element from array
- How to convert json array into Postgres int array
- How to cast json array to text array?
- How should I configure a pathfinding algororithim for my new level generation program
- generating two orthogonal vectors that are orthogonal to a particular direction
- Creating generic array in Java via unchecked type-cast
- How to get n number of random values from an array and prevent consecutively repeated values
- sort 2D array based on two columns
- Can I append an array to 'formdata' in javascript?
- brace elision on struct template doesn't work like std::array
- Element count of an array in C++
- How to create a counting generator/iterator cascade?
- Pyspark: Subset Array based on other column value
- JS How to loop through an array and slice/create a new array at every empty element in the array?
- Declare an empty two-dimensional array in Javascript?
- Compare two arrays of objects, and remove if object value is equal
- Search for PHP array element containing string
- How to create a numpy array of lists?
- How to search in an array with preg_match?
- Powershell Object to json array , mimic a Table/CSV
- array_splice() returns removed data instead of the modified array
- Insert a new associative element into an associative array and preserve keys
- Insert a new element at a specified position in an associative array and preserve keys