How to contain the LOOKUPVALUE operation to a set of rows with the same ID?

[peterpm]

Hi all, I have a few hundred thousand rows with names in two columns.

I'm trying to get the following output:

ID	Names	Surnames	Unique Names	Unique Surnames
1	Anna	Jorge	Anna	Jorge
1	Richard	Michael
1	Michael	Richard
2	Hammadh	Anna	Hammadh	Anna

I'm using a LOOKUPVALUE formula:

IF(LOOKUPVALUE(Table1[Surnames],Table1[Surnames],Table1[Names])=BLANK(),Table1[Names],BLANK())

This works in evaluating the names in the Names column, comparing with the Surnames, and returning the unique values. If there's only 1 single ID.

However, I need to contain the operation of the LOOKUPVALUE based on the ID. That way, Anna would show up twice as in the above table, but currently, it doesn't since it ignores IDs and does locate the matching surname on ID: 2

How can I contain this LOOKUPVALUE to the rows with the same ID only, as opposed to evaluating the entire column regardless of ID?

Thanks!

[rubayatyasmin]

Hi, @peterpm 

Add the ID column in the lookup value like this,

IF(
LOOKUPVALUE(
Table1[Surnames],
Table1[Surnames], Table1[Names],
Table1[ID], Table1[ID]
) = BLANK(),
Table1[Names],
BLANK()
)