Compare 2 Lists Return Matches
Just trying compare two lists in columns A B and look for text and integers that are matches in column C. Not sure what function makes sense xlookup or index/match? I only have 2019 excel capabilities.
| List 1 | List 2 | Results |
|---|---|---|
| Apple | Pear | Apple |
| Pear | Apple | 1 |
| Kiwi | 3 | 2 |
| 1 | 2 | 3 |
| 2 | 1 | .123Banana |
| 3 | .123Banana | Pear |
| Strawberry | XYZ | |
| Melon | .123Kiwi | |
| .123Banana | ||
| ABC | ||
| .123KiwU |
Solution
Common Values in Two Columns
MS365, Excel 2021 (Spills)
=FILTER(A2:A12,ISNUMBER(XMATCH(A2:A12,B2:B9)))
Older Versions (Copy Down)
=IFERROR(INDEX($A$2:$A$12,SMALL(IF(ISNA(MATCH($A$2:$A$12,$B$2:$B$9,0)),"",ROW($A$2:$A$12)-ROW($A$2)+1),ROW(A2)-ROW($A$2)+1)),"")
- Codeigniter parentheses in dynamic Active Record query
- How to send cart data in hidden field and fetch it in codeigniter controller
- Sorting an array of objects by a property
- Sorting objects in array
- Codeigniter Insert Array to Database
- How to display an array of values in a table with javascript
- C# ToArray conversion merged into one line with resulting array element change
- accessing values in an ARRAY STRUCT in Google Bigquery
- Sort an object array by a column
- Push a new object into a sorted objectArray at the correct position to avoid re-sorting
- How do you natsort() by a column of an array of objects?
- How to create JSON Schema for an array of dictionaries with different type values
- Naturally sort a flat array without preserving original keys
- Create array of values from a dictionary of array of objects filtered by property
- How can I sort an alphanumerical Multidimensional Array in php?
- Naturally sort the rows of a 2d array by a column of alphanumeric values
- Sort an array of alphanumeric strings naturally
- Sort an array of strings by their 3rd character, then 4th, then 2nd
- Sort array based only on trailing numbers in strings
- Custom sort a flat array of scalar values so that integers are given top priority
- sort() prints 1 instead of the sorted array
- How to naturally sort a flat array of filenames (some ending with numbers, e.g. "-2")?
- Is there a shortcut way to return an array sorted in C#
- How to implement multiple custom sorting rules based on hyphen-delimited substrings?
- How can I access data of object stored in an array?
- How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
- How to fix 'Private field must be declared in an enclosing class'
- How can I write a Zig function that can accept and return strings?
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- Including more commands if CSV column is not empty