Collect a unique identifier if a match is found

Joyce Woram

I am trying to use the collect formula with contains. Basically I want to use a unique identifier that is on one row and collect it to another row if the Teacher ID is contained in another row.

For example:

row 1:

New Unique ID: where I want to formula to populate

Unique ID: blank

Teacher ID: 1234

Combined Teacher ID: blank

row 2:

New Unique ID:

where I want to formula to populate

Unique ID: ABC101

Teacher ID: 12341235

Combined Teacher ID: 12341235

I want the New Unique ID to both equal ABC101 in both row since the Teacher ID 1234 is found in row 2.

I tried the following, but the contain portion isn't working:

=IFERROR(INDEX(COLLECT([CCAP No]:[CCAP No], [Combined Teacher ID]:[Combined Teacher ID], CONTAINS([Teacher ID]@row,@cell) ), 1), "")

I also tried

=IFERROR(INDEX(COLLECT([CCAP No]:[CCAP No], [Combined Teacher ID]:[Combined Teacher ID], CONTAINS([Teacher ID]@row,[Combined Teacher ID]:[Combined Teacher ID]) ), 1), "")

Both don't work. Any assistance would be greatly appreciated! TIA

Paul Newcome

Are you able to provide screenshots for context (mock / sample data is fine)? When you say it "isn't working", are you getting an error or an unexpected output? Have you tried removing the IFERROR piece to see if there are any error messages being masked?