This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 72%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERF%3C/text%3E%3C/svg%3E)
I have two Excel sheets. In Sheet1, I have data in columns A, B, C, and D.
Column D contains usernames in each row — some rows have a single username (e.g., user1), while others have multiple usernames separated by semicolons (e.g., user1; user2;), some dont have semicolon.
In Sheet2, I have a list of individual usernames (e.g., user1) in column A.
I want to match each username in Sheet2 (column A) against the values in Sheet1 (column D) — whether the username appears alone or as part of a semicolon-separated list — and then export the matching rows from Sheet1 into Sheet3. Exported data should contain Column(A,B,C,D) screenshot 3 for reference.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
I'm pretty sure VLOOKUP is not the tool to satisfy this requirement.
You are copying rows from sheet1 to sheet3 but only when one of the names in column D also appears somewhere in column A of sheet2. If there is a row in sheet1 without a name match, you do not copy it and you do NOT leave the corresponding row in sheet3 blank. Thus your example shows row 6 copied to row 4.
The function that can determine if one of the names from sheet2 is present in the text of D2 is FIND (or SEARCH). For example, the formula
=IF(SUM(IFERROR(FIND(sheet2A$2:sheet2!A$4,sheet1!D2),0)),sheet1!A2:sheet1!D2,"")
in sheet3!A2 will produce row 2 of you sample output.
Copying this cell down to A8 will produce the data you want but not in the rows you want. There is no way for the formula A6 to know that rows 4 and 5 are blank and the data for Site5 should be moved up to row 4 instead of row 6 where the formula is located.
One obvious solution at this point is to sort A2:D8 (probably on column A) to get the blank lines to the bottom. Another is to create a filter encompassing A2:D8 and deselect blanks in column A.
By the way, if it makes things any easier, you can replace the empty string at the end of the formula with any value you like.