excelexcel-formula

How to add corresponding values to a different sheet with duplicate values (while keeping the duplicates intact)?


Microsoft Excel for Microsoft 365 MSO Version 2505

In my Unique GUIDs sheet (first image), I have values under the column "SL_SubfeederID" which correspond with the unique values under the "SL_GUID" column to the left. Similarly in this sheet, the values under the "SLP_SubfeederID" column correspond with those under the "SLP_GUID" column just to the left.

In my With Duplicates Copy sheet (second image), I have these "SL_GUID" and "SLP_GUID" columns which have their values duplicated across different rows, which is what I want—I do not yet have the corresponding SubfeederID values in this sheet, but am trying to add them here.

I am trying to copy the values that I have under the SL_SubfeederID & SLP_SubfeederID columns (from the Unique GUIDs sheet) over into the With Duplicates Copy sheet so that they correspond with the same SL_GUID & SLP_GUID values, but I want their records duplicated as needed to match with how the SL_GUID & SLP_GUID columns are shown on that sheet. Is there some method of linking these columns together so that I can have their duplicated corresponding values all reflected in the same sheet?

Thanks very much


Solution

  • For this sample data:

    enter image description here

    To get this result:

    enter image description here

    Use these formulas:

    [B2]=XLOOKUP(A2:A5,'Unique GUIDs'!A2:A6,'Unique GUIDs'!B2:B6)
    [D2]=XLOOKUP(C2:C6,'Unique GUIDs'!C2:C5,'Unique GUIDs'!D2:D5)