Excel Return Whole row when it Matches Cell

Question

I have a workbook with two sheets.

Sheet 1 has all of the data.

Sheet 2 is currently blank and I am planning to use a VLOOKUP to return any row that matches the certain cell.

In sheet 1 in column E there are different values in each cell, I want to return any that say tyre

I want them to copy the whole row of data whenever column E contains the word tyre. The word tyre is in Cell B1 in Sheet2

I have currently tried this code which is in sheet 2 but just getting a #VALUE! error.

 =VLOOKUP($B$1,'sheet1'!E:E,0,FALSE)

Answer 1

I would rather use a VBA approach. Just run a macro which says:

Public Sub specialLookUp()
    Dim keyword As String: keyword = Sheets("sheet2").Range("B1").Value
    Dim countRows1 As Long, countRows2 As Long
    countRows1 = 2 'the first row of your dataset in sheet1
    endRows1 = 1000 'the last row of your dataset in sheet1
    countRows2 = 2 'the first row where you want to start writing the found rows
    For j = countRows1 To endRows1
        If Sheets("sheet1").Range("E" & j).Value = keyword Then
            Sheets("sheet2").Rows(countRows2).Value = Sheets("sheet1").Rows(j).Value
            countRows2 = countRows2 + 1
        End If
    Next j
End Sub

Please note that now you can hardcode the beginning and the end of your dataset in sheet1, since you've told me your data have not an ID or any kind of field which is mandatory.