Get values that are repeated in a range two times or more in Google Sheets

Question

I have multiple columns of ticker names in the range A2:D like this:

A	B	C	D
ticker1	ticker2	ticker3	ticker4
AAPL	BA	AMZN	BA
BA	DAL	AMD	F
COST	F		Z

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:

F	G
repeated two times or more	repeated three times or more
BA	BA
F

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 A→B:E, B→C:E, C→D:E and D→E, and then combine the results and delete repeats. I have not been able to get the A→B:E to work correctly.

Answer 1

If it does not really matter whether the repeated values are in different columns, you can do this with filter(countif()) and flatten() like this:

=unique( filter( flatten(A2:D4), countif(A2:D4, flatten(A2:D4)) >= 2 ) )

=unique( filter( flatten(A2:D4), countif(A2:D4, flatten(A2:D4)) >= 3 ) )

If you want the result be sorted, wrap these formulas in sort().