VLOOKUP / Range?

[Watsoa]

Hi guys,

I want to perform a lookup function that will find a given value within a range. Ie, user inputs 100 to a cell, and lookup will retrieve an approximate match +-5 from column 1 and display adjacent cell in column 2.

A standard vlookup true statement with a range i guess? (search for approximately 100 between 95-105).

Im still learning VBA so any help would be brilliant.

Thanks
A

 

[GlennUK]

Here's something to get you started:

Function vlookuptol5(needle, searchrange As Range)
    Application.Volatile
    vlookuptol5 = "Not found"
    For Each c In searchrange
        If Abs(c.Value - needle) <= 5 Then
            vlookuptol5 = c.Offset(0, 1).Value
            Exit For
        End If
    Next
End Function

... used in a cell like this:
=vlookuptol5(100,A2:A60)

 

[Jon von der Heyden]

Please stick with your original thread: http://www.mrexcel.com/forum/showthread.php?t=533245