比较两个工作表

Question

这是一个MS Excel公式问题：

我想要比较两个电子表格的客户姓名和汽车型号。我可以将月度数据导入到新工作表中。我想将Sheet1！ColumnA(客户名称)和Sheet1！ColumnB(汽车型号)与Sheet2！ColumnA(客户名称)和Sheet2！ColumnB(汽车型号)进行比较。当两个变量都匹配时，在Column3(date)中从Sheet2返回相应的单元格。如果不匹配，则保留为空。

我相信它是一串逻辑函数，但我已经尝试了至少20个，但都没有成功。提前感谢！

Answer 1

Sheet1：

​

​

输出：

​

​

尝试：

Option Explicit

    Sub test()

        Dim i As Long, j As Long
        Dim LastrowS1 As Long, LastrowS2 As Long
        Dim Customer_NameS1 As String, Car_ModelS1 As String, Customer_NameS2 As String, Car_ModelS2 As String
        Dim DateS1 As Date

        LastrowS1 = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
        LastrowS2 = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row

        For i = 2 To LastrowS1
            With ThisWorkbook.Worksheets("Sheet1")
                Customer_NameS1 = .Range("A" & i).Value
                Car_ModelS1 = .Range("B" & i).Value
                DateS1 = .Range("C" & i).Value
            End With
                For j = 2 To LastrowS2
                    With ThisWorkbook.Worksheets("Sheet2")
                        Customer_NameS2 = .Range("A" & j).Value
                        Car_ModelS2 = .Range("B" & j).Value
                    End With

                    If Customer_NameS1 = Customer_NameS2 And Car_ModelS1 = Car_ModelS2 Then
                        Sheet2.Range("C" & j).Value = DateS1
                        Exit For
                    End If

                Next j

            Next i

    End Sub