Attempting to use a an IF formula to dynamically copy cell contents given the presence of a specific value
Using an IF formula to achieve this is going to be problematic for a number of reasons. But assuming you got it to work, you're always going to have the issue that a formula has to be placed within a cell and therefore your workbook is not scalable (i.e. what happens over time if/when you add/delete rows)? Sure, you can work around that too (e.g. by formatting your data as a proper table/list), but from my experience you'd be better to use a macro to do this.

Step 1: Add the Developer tab to Excel

First of all you need to ensure that you have the Developer tab visible in Excel's ribbon. If it's already there (typically it's the last one after the View tab, then skip to Step 2: Creating your macro. Otherwise, follow the steps below:


Launch MS Excel
Go to Excel > Preferences (or just press the command, keys)
Select the Ribbon &amp; Toolbar icon
Ensure the Ribbon tab is selected at top
On the right-hand side you'll see a list of tabs - select the checkbox next to the Developer tab (it's probably listed at the very bottom)
Click on the Save button
Exit Preferences
Now you'll see the Developer tab (see image below):




Step 2: Creating your macro

Now we need to create your macro. Follow these steps:


In Excel, make sure you have your workbook open
Select the Developer tab
Click on the Visual Basic option (at far left of the Ribbon)
This will open the Microsoft Visual Basic window
The top left of the window should look something like:

Now right-click on ThisWorkbook and select Insert > Module (see below)





In a few moments you should see a white window open on the right-hand side of the Visual Basic Editor
Now copy the code below and paste it into the white space:

Sub CopyRowsBasedOnCellValue()
'This macro copies rows from one worksheet to another within the same workbook if that row contains a specific value in Column A
    Dim xRg As Range
    Dim xCell As Range
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    X = Worksheets("Sheet1").UsedRange.Rows.Count
    Y = Worksheets("Sheet2").UsedRange.Rows.Count
    If Y = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then Y = 0
    End If
    Set xRg = Worksheets("Sheet1").Range("A1:A" &amp; X)
    On Error Resume Next
    Application.ScreenUpdating = False
    For Z = 1 To xRg.Count
        If CStr(xRg(Z).Value) = "foo" Then
            xRg(Z).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" &amp; Y + 1)
            Y = Y + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Now you should see something like the following:





Now you will need to customise this code slightly as it's currently written specifically for the example in your question. You need to customise the following aspects:


Replace the two instances of Sheet1 with the name of your first worksheet (i.e. the one you're copying from)
Replace the three instances of Sheet2 with the name of your second worksheet (i.e. the one you're copying to)
The instance of A1:A indicates the column being searched for the value you're trying to match (i.e. the "foo" value in your example. If it's not Column A in your worksheet, then change the A to whatever column it needs to be. For example, if your worksheet contains the "foo" value in Column D, then this needs to be D1:D
Finally, you will see a single instance of "foo" in the code. Replace the foo with whatever value you're trying to match.

Finally, you will need to save your workbook as a macro-enabled one. To do this, click on the Excel icon at top left to return to Excel and then choose the Save As option from the File menu. For the file format you must select the Excel Macro-Enabled Workbook (.xlsm) option from the drop-down list.


Step 3: Using the macro

Now you're set to go. To use the macro, follow these steps:


Launch Excel and open the workbook
Select the Developer tab
Click on the Macros icon (it should be the second one)
This will bring up the Macro window:
Select the CopyRowsBAsedOnCellValue macro (see below)





Click on the Run button
Your macro will run and all rows matching the criteria will be copied to your destination sheet. Below is an image showing the before and after shots, based on your example:





Note: The above code was adapted from code originally sourced from https://www.extendoffice.com/. I have no affiliation with that website whatsoever.