Finding Matches Across Columns and Writing Corresponding IDs

**student_md** · 08-06-2023, 09:07 AM

I have an Excel spreadsheet where each row contains information about some articles (in columns A:D).
Please find the attachments.

I want to create a summary that shows which article/s includes the related method.
I mean that I want to write the article's ID to the area with a yellow highlight as in the picture.

For example;
I want to check if the value in cell G3 appears in any cell within columns C or D. If a match is found, I want to write the corresponding Article ID (from column A) into cell H3. If there are multiple matches, I want to write the second article_ID to the next column in the same row.
Any help would be greatly appreciated.

find_and_write_ID.xlsx find_and_write_ID.xlsx

**CARACALLA** · 08-06-2023, 09:28 AM

H3=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)-ROW($A$2)+1/($C$2:$D$100=$G3)/($C$2:$D$100<>""),COLUMNS($A$1:A1))),"")

Copy across and down

**windknife** · 08-06-2023, 09:52 AM

Clean all exptected result in G2:M6

G2
=UNIQUE(TEXTSPLIT(TEXTJOIN(",",1,C2:D10),,","))

H2
=TRANSPOSE(FILTER($B$2:$B$10,($C$2:$C$10=G3)+($D$2:$D$10=G3)))

copied down.