how to get common names from 4 different excel sheets via excel formula

Hi there! My name is David. It’s a pleasure to help you today.Regarding your question, it’s possible to do this in two ways. I can suggest the following formulas:

1. If you want to find the names that are repeated across all four sheets, use this formula:

      =TEXTJOIN(", ", TRUE,
        IF(
          (Sheet1!$A$2:$A$100<>"")*
          (COUNTIF(Sheet2!$A$2:$A$100, IFERROR(Sheet1!$A$2:$A$100,""))>0)*
          (COUNTIF(Sheet3!$A$2:$A$100, IFERROR(Sheet1!$A$2:$A$100,""))>0)*
          (COUNTIF(Sheet4!$A$2:$A$100, IFERROR(Sheet1!$A$2:$A$100,""))>0)*
          (IFERROR(MATCH(Sheet1!$A$2:$A$100, Sheet1!$A$2:$A$100, 0),0)
             =ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),
          Sheet1!$A$2:$A$100, ""
        )
      )
   

2. On the other hand, if you want to find names that appear in any of the four sheets, use the following formula:

      =TEXTJOIN(", ", TRUE,
        IF(
          (LEN(TRIM(Sheet1!$A$2:$A$100))>0)*
          (Sheet1!$A$2:$A$100<>0)*
          (Sheet1!$A$2:$A$100<>"0")*
          (
            SIGN(COUNTIF(Sheet2!$A$2:$A$100, IFERROR(Sheet1!$A$2:$A$100,""))) +
            SIGN(COUNTIF(Sheet3!$A$2:$A$100, IFERROR(Sheet1!$A$2:$A$100,""))) +
            SIGN(COUNTIF(Sheet4!$A$2:$A$100, IFERROR(Sheet1!$A$2:$A$100,"")))
          )>=1 *
          (IFERROR(MATCH(Sheet1!$A$2:$A$100, Sheet1!$A$2:$A$100, 0),0)
             = ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),
          TRIM(Sheet1!$A$2:$A$100),
          ""
        )
      )
   

Keep in mind that in the second case, if a name from Sheet1 appears in Sheet3, it will return a result. If it also appears in Sheet2, it will return a result again. In this scenario, the formula does not require the names from Sheet1 to appear in all four sheets to return a match.

Remember to adjust the ranges and sheet names. In the formulas I shared with you, the range starts from row A2 assuming you might have headers and goes down to row A100.

It’s important to note that depending on your version of Excel, when entering these formulas, you should not press Enter directly. Instead, use the key combination Ctrl + Shift + Enter to convert the formula into an array formula so that the search spans all four sheets. You’ll know it worked if the formula bar starts with a “{”.

Let me know if the shared information has been helpful. Regards.