Hi. I have two columns of data: for example E3:E7 and V3:V12. I want to identify whether any of the cells in the first column is the same as any of the cells in the second column. I’ve used multiple ‘OR’ formulas of the type =OR(E3=V$29,E3=V$30,E3=V$31,E3=V$32,E3=V$33,E3=V$34,E3=V$35,E3=V$36,E3=V$37,E3=V$38,E3=V$39,E3=V$40,E3=V$41,E3=V$42,E3=V$43,E3=V$44,E3=V$45,E3=V$46,E3=V$47,E3=V$48,E3=V$49,E3=V$50,E3=V$51,E3=V$52,E3=V$53,E3=V$54)
to give a true / false and copied these into cells D3:D7.
This is workable for a small section of the spreadsheet but I need to compare over a thousand entries in each column, which makes the resulting cell coding rather large.
I’ve tried using ARRAY in various guises but can’t find a solution.
I’d be grateful for any suggestions.
Thanks. Don
Enter
=OR(E3=V$29:V$54)
as array formula, i.e. close cell input with Shift+Ctrl+Enter instead of just Enter.
See also Array Functions .
Hello erAck,
Many thanks for that. It works perfectly. I thought it was one of the things I’d tried but, obviously, I hadn’t. You’ve saved me a lot of unnecessary work and I really appreciate it.
Don
If actually both “any” are meant as I would understand them, the formula in cell K3 of the attached example should work well.
disask80408specialComparison.ods (9.2 KB)
But if you actually want to know which ones of the cells E3:E7 hold a value that reoccurs in the second range, you need the multiple formulas, of course.