Forum Discussion
How to auto-populate all values of the row in a new excel sheet if two keywords are found in the row
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...
=LET(tables,XFD1:XFD5,
DROP(REDUCE("",SEQUENCE(ROWS(XFD1:XFD5)),
LAMBDA(x,y,VSTACK(x,IFERROR(FILTER(INDIRECT("'"&INDEX(tables,y,)&"'!A2:F15"),
BYROW(INDIRECT("'"&INDEX(tables,y,)&"'!A2:F15"),
LAMBDA(z,(COUNTIF(z,"Card")=1)*(COUNTIF(z,"complete")=1)))),{"".""."".""."".""})))),1))
DROP(REDUCE("",SEQUENCE(ROWS(XFD1:XFD5)),
LAMBDA(x,y,VSTACK(x,IFERROR(FILTER(INDIRECT("'"&INDEX(tables,y,)&"'!A2:F15"),
BYROW(INDIRECT("'"&INDEX(tables,y,)&"'!A2:F15"),
LAMBDA(z,(COUNTIF(z,"Card")=1)*(COUNTIF(z,"complete")=1)))),{"".""."".""."".""})))),1))
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.