Forum Discussion
Pulling exact same data across multiples tables
=AND(COUNTIFS($F$3:$F$10,A3,$G$3:$G$10,B3,$H$3:$H$10,C3,$I$3:$I$10,D3)>0,
COUNTIFS($A$14:$A$21,A3,$B$14:$B$21,B3,$C$14:$C$21,C3,$D$14:$D$21,D3)>0,
COUNTIFS($F$14:$F$21,A3,$G$14:$G$21,B3,$H$14:$H$21,C3,$I$14:$I$21,D3)>0)
You can apply AND along with COUNTIFS. The formula returns TRUE (WAHR in german Excel) for the rows of data which occur in all 4 tables. The ranges within COUNTIFS can be adapted as required.
=FILTER(A3:D10,E3:E10)
If you have access to the FILTER function you can apply it to select only the rows that occur in all 4 tables.
=IFERROR(INDEX(A$3:A$10,SMALL(IF($E$3:$E$10=TRUE,ROW($A$3:$A$10)-2),ROW($A1))),"")
If you don't have access to the FILTER function you can use this formula to select only the rows that occur in all 4 tables. The formula is in cell K7 and filled across range K7:N10. The formula must be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.