VBA lookup for approximate value

Question

I want to perform a special VLookup where the value which is found would match two conditions:

1. The invoice number must be the same
2. The value found from Column G must be within the tolerance -100 to 100

Precisely speaking, if the first value found from Column G (e.g. -18,007) for invoice number '12345678' does not match the 2nd criteria (e.g. -18,007 + 10,000 = -8,007), -8,007 is outside the tolerance, so go to find the next value for '12345678', until it matches the 2nd criteria. Is that possible?

Below is my script:

Sub MyVlookup()

    Dim lastrow As Long
    lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    Set myrange = Range("D:G")

    For i = 2 To lastrow

        Cells(i, 10) = Application.WorksheetFunction.VLookup(Cells(i, 2), myrange, 4, False)

        'This following line is to test the value found is within the tolerance -100 to 100
        If (Cells(i, 10) + Cells(i, 1)) >= 100 Or (Cells(i, 10) + Cells(i, 1)) <= -100 Then

            Cells(i, 10).Value = "False" '<----I want to change this line to Lookup the next invoice number in Column D of table2

        Else: Cells(i, 10) = Application.WorksheetFunction.VLookup(Cells(i, 2), myrange, 4, False)

        End If

    Next i

End Sub

Edit

The final output I want:

Below is the script working with my amendments, but need to be checked:

Sub MyVlookup2()

    Dim myrange As Range
    Dim i As Long, j As Long
    Dim lastrow As Long
    Dim lastrow2 As Long  
    Dim diff As Double
    Const tolerance As Long = 100
    Set myrange = Range("D:G")
    lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    lastrow2 = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row

    For i = 2 To lastrow
    For j = 2 To lastrow2
         If Cells(i, 2).Value = Cells(j, 4).Value Then
            diff = Cells(i, 1).Value + Cells(j, 7).Value
               If diff <= tolerance And diff >= -tolerance Then
                  Cells(i, 9).Value = Cells(j, 4).Value
                  Cells(i, 10).Value = Cells(j, 5).Value
                  Cells(i, 11).Value = Cells(j, 6).Value
                  Cells(i, 12).Value = Cells(j, 7).Value
               Exit For
            End If
         End If
      If j = lastrow2 Then Cells(i, 10).Value = False
    Next j
    Next i

 End Sub

Answer 1

This should work (I decided not to use worksheetfunction.vlookup):

Sub MyVlookup2()

Dim myrange As Range
Dim i As Long, j As Long
Dim lastrow As Long
Dim lastrow2 As Long
Dim diff As Double
Const tolerance As Long = 100
Set myrange = Range("D:G")
lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
lastrow2 = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
    For j = 2 To lastrow2
        If Cells(i, 2).Value = Cells(j, 4).Value Then
            diff = Cells(i, 1).Value + Cells(j, 7).Value
            If diff <= tolerance And diff >= -tolerance Then
                Cells(i, 10).Value = Cells(j, 7).Value
                Exit For
            End If
        End If
        If j = lastrow2 Then Cells(i, 10).Value = False
    Next j
Next i

End Sub

---

Regarding the Option Explicit, you should check the checkmark in Tools > Options... and never bother about it again. The line will be always automatically included in every new module.

Edit

Since you updated your question, if you don't change the line If j = lastrow2 Then Cells(i, 10).Value = False, you will have blank values where a match is not found: