How to auto-populate all values of the row in a new excel sheet if two keywords are found in the row

Copper Contributor

Hello all, 


I want to know if it’s possible to auto populate all data present in a row in a new excel sheet if the row from which I need to copy has two keywords.


for instance if the workbook from which I am copying is having keywords like CARD and complete, how can I make sure that the whole row gets copied to a new sheet as soon as these words are triggered in the row when someone inputs these words in the sheet from which I want to copy.


also is it possible to set it up for multiple sheets. For instance I have a folder which has multiple excel spreadsheets, is it possible to pull this data from multiple spreadsheets to a single sheet if these keywords are found within those sheets in the folder.


thank you in advance for the help it would be a great help please.

3 Replies


If you have Microsoft 365, you can use the FILTER function to return rows from a worksheet that satisfy some conditions, and VSTACK to combine the result of FILTER from multiple worksheets.

If you'd like help with that, please provide more detailed information. For example: in which column (or columns) should we look for the keywords?


Let's say the sheetnames are in range XFD1:XFD5 e.g. sheet1, sheet2, sheet3, sheet4 and sheet5. This formula performs an exact match in each sheet and checks in each row of range A2:F15 if the keywords Card and complete are found. If both keywords are found once within a row the formula returns the row in the output sheet. If within a certain sheet there isn't a row which contains both keywords the formula returns an empty row.
exact match in rows of multiple sheets.png
Share some data to work with and show the expected result.

pull this data from multiple spreadsheets to a single sheet

if these keywords are found
apply sql where to filter.