excel - 通过查找找到最接近的号码？

Question

有没有办法从表中找到最接近的数字。

示例 - 我有以下一组数字

Column 1    Column 2
 € 1,187,385     € 19,547.82 
 € 949,430   € 30,404.75 
 € 935,216   € 19,704.01 
 € 907,508   € 28,912.47 
 € 865,841   € 21,698.54 
 € 844,429   € 26,468.55 
 € 741,769   € 26,687.78 
 € 729,647   € 28,750.40 
 € 711,840   € 11,909.60 
 € 690,197   € 22,172.20 
 € 659,998   € 15,919.33 
 € 657,956   € 9,134.46 
 € 648,282   € 13,700 
 € 634,949   € 7,802 
 € 631,214   € 6,407 

我的查找值为 730,000 - 因为这不是 100% 匹配 - 有没有办法找到与该数字 (730,00) 最接近的匹配 - 意识到它是 729,647 然后告诉 excel 显示该数字在第 2 栏中 (28,780.40)

再举几个例子：

如果我搜索 A 列中的数字 - 结果将如下所示。

 € 730,000   € 26,687.78 
 € 1,239,636     € 19,547.82 
 € 693,365   € 22,172.20 
 € 631,283   € 6,407 

真正匹配的 Vlookup

标签： excelexcel-formula

Answer 1

你可以使用：

Option Explicit

Sub test()

    Dim i As Long, j As Long, LastRowA As Long, LastRowD As Long
    Dim Value As Long
    Dim Differ As Long
    Dim Result As Double

    With ThisWorkbook.Worksheets("Sheet1")

        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
        LastRowD = .Cells(.Rows.Count, "D").End(xlUp).Row
        Differ = 0

        For i = 2 To LastRowD

            Value = .Range("D" & i).Value

            For j = 2 To LastRowA

                If j = 2 Then
                    Differ = Abs(Value - .Range("A" & j).Value)
                    Result = .Range("B" & j).Value
                Else
                    If Abs(Value - .Range("A" & j).Value) < Differ Then
                        Differ = Abs(Value - .Range("A" & j).Value)
                        Result = .Range("B" & j).Value
                    End If
                End If

            Next j

            .Range("E" & i).Value = Result

        Next i

    End With

End Sub

结果：