How do I search and import data from one spreadsheet to another?

To compare and import data from one spreadsheet to another in Excel, you can use the VLOOKUP function or the XLOOKUP function (if you have a newer version of Excel). Here’s a step-by-step guide:

1. Open Both Spreadsheets: Ensure that both Sheet A (with employee names) and Sheet B (with employee names and supervisors) are open in Excel.
2. Add a New Column in Sheet A: In Sheet A, add a new column where you want to import the supervisor names from Sheet B.
3. Use VLOOKUP or XLOOKUP:
   • For VLOOKUP: In the new column in Sheet A, enter the following formula:

          =VLOOKUP(A2, 'Sheet B'!A:B, 2, FALSE)
     

     Here, A2 is the cell in Sheet A containing the employee name, 'Sheet B'!A:B is the range in Sheet B where the employee names and supervisors are located (adjust the range as necessary), 2 indicates that you want to return the value from the second column (supervisor names), and FALSE ensures that you are looking for an exact match.
   • For XLOOKUP: If you have XLOOKUP available, you can use:

          =XLOOKUP(A2, 'Sheet B'!A:A, 'Sheet B'!B:B, "Not Found")
     

     This will return the supervisor's name from Sheet B corresponding to the employee name in A2. If no match is found, it will return "Not Found".
4. Drag the Formula Down: After entering the formula in the first cell of the new column, drag the fill handle down to apply the formula to the other cells in the column.
5. Review the Results: Check the new column to ensure that the supervisors have been correctly imported next to the corresponding employee names.
6. Handle Errors: If there are any errors (like "Not Found"), you may need to check for spelling discrepancies or variations in employee names between the two sheets.

This method will help you efficiently compare and import the list of supervisors from Sheet B to the appropriate employee records in Sheet A.

References:

• Import from an Excel or CSV file
• Import Data from Excel to Master Data Services (MDS Add-in for Excel)
• Import your organizational data with a .csv file
• ImportExportSpreadsheet macro action
• ImportExportSpreadsheet macro action

Hi @Yarberry, Jeffrey - MRP-APHIS,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your information, you want to import the list of supervisors from Sheet B to their according employee's names in Sheet A.

• If each of your employees only has one specific supervisor, you can try using the VLOOKUP/XLOOKUP formula which was proposed in the previous reply of Q&A Assist.
• If each of your employees is supervised by multiple supervisors, you need to use this formula:

=IFERROR(TEXTJOIN(", ", TRUE, FILTER(Sheet2!$A:$A, Sheet2!$B:$B=A2)),"No Supervisors")

Note: this formula uses the data range in my test worksheet. Therefore, you need to adjust the data range to your current worksheet to make the formulas work correctly.

Since this is the data I created for testing purposes, its structure may be not as the same as your Excel structure. For further assistance, please share some sample screenshots of your Excel worksheets so I can take a look and clearly understand your situation to provide the most appropriate solutions to your concern.

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out in the comments of this post. I'd be happy to assist you.    

Looking forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".          

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread