I have multiple columns of ticker names in the range
A2:D like this:
The goal is to return cells that appear twice (in two different columns), and separately, to return cells if appears thrice (in three different columns), like this:
|repeated two times or more||repeated three times or more|
I have a sample spreadsheet with sample columns and desired results.
I have tried the following formulas, but I cannot figure out how to search across all possibilities.
=IF(COUNTIF($B:$E, $A2:$A20)>0, $A2:$A20, ""): returns only one match from the first column
=VLOOKUP($A2:$A20, $B2:$E50, 1, true): returns only one match as well and the data isn't sorted. If I use
false instead of
true I get no results.
=QUERY($A2:$E50, "select A where A matches B:E label A 'Multis'", 0): only returns results if they are in the same row, i.e., when A3 matches C3, but not when A3 matches C4.
None of these formula can see if column B matches column C. If I could get any of these to work I could nest searches
D→E, and then combine the results and delete repeats. I have not been able to get the
A→B:E to work correctly.