Excel查询：搜索全表指定值并返回其上方第7个单元格内容

方法1：Excel 365/2021+ 专属公式 #

利用FILTER+OFFSET组合直接筛选结果：

=FILTER(OFFSET(A1:XFD1,ROW(A1:XFD1048576)-8,0,1,COLUMNS(A1:XFD1)),ISNUMBER(SEARCH("VALUE A",A1:XFD1048576)))

• 把"VALUE A"替换为你的目标搜索值
• OFFSET负责定位到匹配单元格上方第7行的对应单元格（ROW()-8是偏移量计算逻辑：当前行号减7即为目标行，OFFSET需用行号减8作为偏移参数）
• FILTER会自动输出所有符合条件的结果

方法2：旧版Excel数组公式 #

若不支持FILTER，用数组公式实现：

=INDEX(A:XFD,SMALL(IF(ISNUMBER(SEARCH("VALUE A",A:XFD)),ROW(A:XFD)-7,""),ROW(A1)),COLUMN(A:XFD))

1. 输入公式后按Ctrl+Shift+Enter触发数组计算
2. 下拉公式直到出现#NUM!，所有有效结果会依次显示
3. 替换"VALUE A"为目标值即可

方法3：VBA宏批量处理 #

需要灵活导出结果时，用VBA代码实现：

Sub GetAbove7thCell()
    Dim searchValue As String
    Dim ws As Worksheet
    Dim cell As Range
    Dim resultRange As Range
    Dim resultRow As Integer
    
    searchValue = "VALUE A"
    Set resultRange = ThisWorkbook.Sheets("Sheet1").Range("A1")
    resultRow = 1
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If InStr(1, cell.Value, searchValue, vbTextCompare) > 0 Then
                If cell.Row > 7 Then
                    resultRange.Offset(resultRow - 1, 0).Value = ws.Cells(cell.Row - 7, cell.Column).Value
                    resultRow = resultRow + 1
                End If
            End If
        Next cell
    Next ws
    
    MsgBox "处理完成，共找到" & resultRow - 1 & "条结果"
End Sub

• 修改searchValue为目标值，调整resultRange设置结果输出位置
• 代码会遍历所有工作表的非空单元格，提取符合条件的上方第7行内容并导出

示例验证 #

以搜索V01为例，找到所有包含该值的单元格后，提取其上方第7行内容，最终返回：

• Crème Fraîche 326 µg
• Cheese (Cow's Milk) 224 µg AVERAGE

内容的提问来源于stack exchange，提问作者Frank Braun