匹配单元格时Excel返回整行 [英] Excel Return Whole row when it Matches Cell

问题描述

我有一本有两张纸的工作簿.

I have a workbook with two sheets.

第1张中包含所有数据.

Sheet 1 has all of the data.

Sheet 2当前为空白，我计划使用VLOOKUP返回与特定单元格匹配的任何行.

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

在E列的工作表1中，每个单元格都有不同的值，我想返回任何表示 tyre

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

我希望他们在E列包含轮胎"一词时复制整个数据行.轮胎一词位于Sheet2的单元格B1中

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

我目前已经尝试了此代码，该代码位于工作表2中，但仅获得#VALUE！错误.

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)

推荐答案

我宁愿使用VBA方法.只需运行一个宏，上面写着:

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

请注意，现在您可以在工作表1中对数据集的开始和结尾进行硬编码，因为您已经告诉我您的数据没有ID或任何必填字段.

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.

这篇关于匹配单元格时Excel返回整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！