DOH!! Never mind...
If the MiddleName is NULL in both records, it hasn't changed, and we don't need to update that record.
I'm unclear about how the Isnull formula applies in the following expression:
FirstName != LkUp_FirstName) || ((ISNULL(MiddleName) ? “Humperdinck” : MiddleName) != (ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName)) || (LastName != LkUp_LastName)
I understand the need to "capture" NULLs, but in looking at the above expression, I'm thinking in terms of a record where the middle name is NULL in both the source and destination tables. In this case, I would think that the following results would be generated for the MiddleName ad LkUp_MiddleName values where the Middle Name is in fact Null in both tables:
(ISNULL(MiddleName) ? “Humperdinck” : MiddleName) => “Humperdinck”
(ISNULL(LkUp_MiddleName) ? “Humperdinck” : LkUp_MiddleName) => “Humperdinck”
And if that's correct, then the result of the comparison operator would be:
“Humperdinck” != “Humperdinck” => FALSE
So, assuming that FirstName and LastName have not been updated, you would get FALSE for all three comparisons, which would of course be a FALSE for the final condition result, and the Lookup Match Output would not include this row, since there are no matches.
That said, it's obvious from the following image that the comparison does work, so can you help me understand what I'm missing?