将一列中的单元格与另一页中的另一列匹配

I have 2 sheets - one has one column for date and another for the amounts and the other sheet has a column for the date and the amounts plus another column that has description of each amount. *****How can match these 2 columns of amounts**? I want a formula taht tells me which cell on the first sheet I can find a certain amount that also exists on the other sheet.

我有两张纸 - 一张有一列用于日期,另一张用于金额,另一张有一列用于日期和金额加上另一列有每种金额的描述。 *****如何匹配这两列金额**?我想要一个公式告诉我在第一张纸上哪个单元格我可以找到另一张纸上也存在一定数量的单元格。

Thanks a lot if someone can help me

非常感谢有人可以帮助我

3 个解决方案

#1

I'm not sure I understand your question, but it sounds like you want to look at the MATCH and/or VLOOKUP worksheet functions. (MATCH can tell you the position of a specific value in a list of values, and VLOOKUP can find a value in a column, and then give you the value from the same row in a different column.)

我不确定我理解你的问题,但听起来你想看看MATCH和/或VLOOKUP工作表函数。 (MATCH可以告诉您值列表中特定值的位置,VLOOKUP可以在列中找到值,然后从不同列中的同一行中提供值。)

#2

How about ADO?

ADO怎么样?

Sub ListMatches()
Dim cn As Object
Dim rs As Object

    'http://support.microsoft.com/kb/246335 '

    strFile = Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    strSQL = "SELECT s2.AcDate, s2.Amount, s1.Description " _
    & "FROM [Sheet2$] s2 INNER JOIN [Sheet1$] s1 " _
    & "ON s2.AcDate=s1.AcDate AND s2.Amount=s1.Amount"

    rs.Open strSQL, cn, 3, 3

    Worksheets(3).Cells(2, 1).CopyFromRecordset rs

End Sub

#3

If you are trying to reference a cell in another sheet, you use the following:

如果您尝试引用另一个工作表中的单元格,请使用以下命令:

='SheetName'!F2

where F2 is the cell you want to retrieve the value of.

其中F2是您要检索的值的单元格。

#1

#2