Compare which values of array1 are present in array2
I'm trying to compare 2 arrays using this formula
=(A1:A5={"String1","String2"})
I'm getting a spilled array of 2 columns like this
How can I get a unique array? I've tried with =OR((A1:A5={"String1","String2"})) but I only get 1x1 array and I'd like to get a 5x1 array like this:
Solution
Convert the spilled array of 2 columns using this
=BYROW(A1:B5, LAMBDA(row, OR(row)))
- How to use a variable to globally define a cell's location?
- "Interface not supported" error when attempting to open Excel or Word from Delphi
- Generate a summary table in Excel for multiple variables without PivotTable
- Array formula on Excel for Mac
- openpyxl: (column) grouping - outline level not working & retaining only last one
- Optimization using Solver in Excel
- Runtime error 5020: Replace content in cells based on contents of a different cell (using regex patterns)
- Excel SUM function is not working (shows 0), but using Addition (+) works
- Export charts from excel to image
- VBA to delete unused named ranges in Excel
- SQL Server export to Excel with OPENROWSET
- Excel - Get hourly averages for data spanning 1 year (8760 data points)
- How to round time to nearest hour in Excel?
- Excel to XML code in java?
- Date difference with VBA InputBox in "Years" "Months" "Days" format
- Extract Certain Text from excel
- excel vba getting the row,cell value from selection.address
- Need help editing VBA to pull from a specific subfolder versus all subfolders
- How to temporarily turn off Conditional Formatting rules in Excel for only selected cells
- Excel FILTER with dynamic conditions
- Problem with ActiveSheet.Paste Link:=True
- Is there a way to extract the text from a cell by referencing the cell?
- Authorization issue while trying to connect to Azure DevOps REST API with MS Excel VBA
- VBA hyperlink replace function out of memory
- A non-VBA recursive factorial function?
- Simple recursive function in VBA with Excel not returning expected result
- Excel VBA attach multiple worksheets in one email
- How can I create a replay loop from different cells?
- Convert an excel or spreadsheet column letter to its number in Pythonic fashion
- Need an excel VBA code to determine whether or not a cell contains an embedded picture