I have a table containing main and secondary values for different documents (each document having its own id as a number which can have decimals).

From this source table, I want to obtain another one with a mapping between values and documents and for each mapping I want to see if the value is "main" or "secondary" for that document in the original table.

I already have the first two columns created in the mapping table, but I can't figure out what formula to use for the 3rd column.

So far I have tried XLOOKUP but it doesn't seem to work because some documents show up more than once in the original table.

Starting table:

Desired result:

CodePudding user response：

Here is one way:

Formula in C8:

=FILTER(A$1:B$1,BYCOL((A$2:B$4=A8)*(ISNUMBER(FIND(","&CHAR(10)&B8&",",","&CHAR(10)&C$2:C$4&","))),LAMBDA(a,SUM(a))))

Or, for older versions:

=INDEX(A$1:B$1,SUMPRODUCT((A$2:B$4=A8)*(ISNUMBER(FIND(","&CHAR(10)&B8&",",","&CHAR(10)&C$2:C$4&","))*COLUMN(A1:B1))))