Formula to paste content of a cell from a different workbook if yet another cell contains one of several words?

[Judykay4444]

Actually what I'm trying to do is copy the entire row over into another sheet if the contents of what's in the first cell (column A) of that row contains "X". but it doesn't have to be an exact match. In fact, that's what's tripping me up. Sometimes it could say CC and sometimes it could be spelled out as Critical Care. So I want the formula to say "if cell A2 contains either the words CC or Critical, then paste the whole row over (or paste cell B2)". If I can't paste the whole row, then I'll do it cell by cell. I think there'd have to be a macro to do the whole row. In fact, I did find someone on youtube who wrote a macro and it worked wonderfully, but the text "CC" had to be an exact match and I couldn't say to please find either/or CC, Critical Care, UC... whichever hospital line I'm looking for which might be referred to in several different ways. So I couldn't use his macro. Now I'm just searching for a formula to put in each cell to pull it cell by cell. I can leave the formulas in the cells and when I'm working in Sheet1 which will have all the information with all the hospitals no matter what service line they are, the cells in the other workbooks will just populate and I can have several different workbooks separating out the individual service lines. I hope.

 

[SunnyAlv]

@Judykay4444 it would be better if you insert example data

 

[Judykay4444]

Actually what I'm trying to do is copy the entire row over into another sheet if the contents of what's in the first cell (column A) of that row contains "X". but it doesn't have to be an exact match. In fact, that's what's tripping me up. Sometimes it could say CC and sometimes it could be spelled out as Critical Care. So I want the formula to say "if cell A2 contains either the words CC or Critical, then paste the whole row over (or paste cell B2)". If I can't paste the whole row, then I'll do it cell by cell. I think there'd have to be a macro to do the whole row. In fact, I did find someone on youtube who wrote a macro and it worked wonderfully, but the text "CC" had to be an exact match and I couldn't say to please find either/or CC, Critical Care, UC... whichever hospital line I'm looking for which might be referred to in several different ways. So I couldn't use his macro. Now I'm just searching for a formula to put in each cell to pull it cell by cell. I can leave the formulas in the cells and when I'm working in Sheet1 which will have all the information with all the hospitals no matter what service line they are, the cells in the other workbooks will just populate and I can have several different workbooks separating out the individual service lines. I hope.