如何将一个表列数据与另一个表列数据进行匹配？

Question

我有2个工作簿，我想要匹配工作簿-1个工作表一个列与另一个工作簿-2个工作表一个列，如果2个单元格是相同的匹配ID，则返回标题。

例如:如果我的"X“标记的ID all与工作簿-1 ID匹配，我想返回工作簿-2标题，如like A001。

预期的结果，如workbook-2中的"Output“表

工作簿-1

​

​

工作簿-2

​

​

结果示例

​

​

Answer 1

我尝试过这一点，尽管它对我来说没有多大意义。它要求您正在使用的三个工作表在屏幕截图中使用确切的工作表名称，并且没有其他工作表共享该名称。我没有测试它，但是

Sub WhatTheHeckisThis()
Dim WKBK As Workbook, wsStart As Worksheet, wsLookup As Worksheet, wsOutput As Worksheet
Dim lineLookup As Range, i As Long, yCell As Range, rCell As Range


Dim tWSstart As String: tWSstart = "Datasheet"
Dim tWSLookup As String: tWSLookup = "MATCH"
Dim twsOutput As String: twsOutput = "OUTPUT"

For Each WKBK In Application.Workbooks
    For Each WS In WKBK.Sheets
        If UCase(WS.Name) = UCase(tWSstart) Then
            Set wsStart = WS
        ElseIf UCase(WS.Name) = (tWSLookup) Then
            Set wsLookup = WS

        ElseIf UCase(WS.Name) = UCase(twsOutput) Then
            Set wsOutput = WS
        End If
    Next WS
Next WKBK


For Each rCell In Intersect(wsStart.Range("B2:B" & Rows.Count), wsStart.UsedRange).Cells

    Set lineLookup = Nothing
    Set lineLookup = wsLookup.Cells.Find(rCell.Value, _
        After:=wsLookup.Cells.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If Not lineLookup Is Nothing Then

        For Each yCell In Intersect(lineLookup.EntireRow, wsLookup.UsedRange).Cells

            If UCase(yCell.Value) = "X" Then
                With wsOutput
                i = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                 .Cells(i, 1).Value = i - 1
                .Cells(i, 2).Value = wsLookup.Cells(1, yCell.Column).Value
                End With

            End If
        Next yCell
    End If

Next rCell

End Sub