Forum Discussion

Patty_M_2023's avatar
Patty_M_2023
Copper Contributor
Sep 19, 2023

Help with comparison formulas

I need to compare data two separate spreadsheets, then report the results in another spreadsheet. For example, I have a list of names on one spreadsheet and a list of classes with names on another spreadsheet. If the names on the one spreadsheet matches the names on the other, I want to pull out all the classes they took.  I can't figure out how to do it.

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 20, 2023

    Patty_M_2023 

    To compare data from two separate spreadsheets and report the results in another spreadsheet in Excel, you can use various lookup and matching functions. In your specific case, you want to compare names in one spreadsheet to names in another and extract classes when there is a match. Here is a step-by-step guide:

    Let us assume you have two worksheets named "Sheet1" and "Sheet2" with the following data:

    Sheet1 (Names):

    • Column A: List of names

    Sheet2 (Classes):

    • Column A: List of names
    • Column B: List of classes

    You want to extract classes for matching names from "Sheet2" and report them in a new spreadsheet, say, "Sheet3."

    Follow these steps:

    1. Create a New Spreadsheet (Sheet3):
      • Create a new worksheet where you want to report the results, let us call it "Sheet3."
    2. Enter the Names to Compare in Sheet3:
      • In "Sheet3," in column A (or any other column you prefer), enter the names you want to compare with the names in "Sheet1."
    3. Use VLOOKUP Function to Extract Classes:
      • In "Sheet3," in column B (or another column), use the following formula in the first cell (e.g., B2):

    =IFERROR(VLOOKUP(A2, 'Sheet2'!A:B, 2, FALSE), "")

    This formula uses the VLOOKUP function to search for the name in cell A2 of "Sheet3" within the range of names and classes in "Sheet2" (assuming names are in column A and classes are in column B). If a match is found, it returns the corresponding class; otherwise, it returns an empty string.

    1. Drag the Formula Down:
      • Drag the fill handle (a small square at the bottom right corner of the selected cell) down to copy the formula for all the names in column A of "Sheet3."

    The result in column B of "Sheet3" will display the classes for matching names from "Sheet2," and it will be empty if there is no match.

    This way, you will have a report in "Sheet3" that shows the classes for the names that match between the two spreadsheets.

     

    If you are looking for an alternative to Excel's Power Query (Get & Transform Data) to compare data from multiple files, you can consider using dedicated data comparison and merging tools. While Excel's Power Query is powerful for many data transformation tasks, specialized tools offer more advanced capabilities for file comparison and merging.

     

    The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources