Forum Discussion

ExcelNewBie's avatar
ExcelNewBie
Occasional Reader
May 18, 2026

Logic Explanation

=IF(
    AND(
        [@[Group(without DM Converted/Created Contact)]] = "Marketing Lead",
        OR(
            A10739 = "John Doe",
            A10739 = "Jane Smith",
            A10739 = "Michael Johnson",
            A10739 = "Emily Davis"
        )
    ),
    IF(
        AND(
            XLOOKUP(
                [@[Converted Contact: Contact Id Safe]],
                'DM with AddOn Created COntacts'!E:E,
                'DM with AddOn Created COntacts'!F:F,
                "Sales Lead"
            ) = "Sales Lead",
            [@[First Form Fill Detail]] = "",
            [@[Last Form Fill Detail]] = ""
        ),
        "Sales Lead",
        "Marketing Lead"
    ),
    [@[Group(without DM Converted/Created Contact)]]
)

Hi Excel Experts. I came across this formula from one of our resigned team members. can somebody explain how the logic works? I am just a simple photoshop user and was assigned to check this.




excel

1 Reply

  • Olufemi7's avatar
    Olufemi7
    Steel Contributor
    May 19, 2026

    Hello ExcelNewBie​,

    The formula first checks whether the row is “Marketing Lead” and whether A10739 matches one of the specified users. If either condition fails, it returns the original group value.

    If both are true, it runs an XLOOKUP on the Converted Contact ID against the DM with AddOn Created Contacts table and returns the matched value or defaults to “Sales Lead” if no match is found.

    It then checks if the lookup result is “Sales Lead” and both First Form Fill Detail and Last Form Fill Detail are blank.

    If all conditions are met, it returns “Sales Lead”, otherwise it returns “Marketing Lead”.