Thread: Return a Value From a Range

Hello try this (copy it into sheet1 code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Integer
Dim val As Double

r = 2 'start row
If Target.Address = "$B$20" Then
    Do Until Cells(r, 1).Value = ""
      If Target.Value >= Cells(r, 1).Value And Target.Value <= Cells(r, 2).Value Then
        Range("B21").Value = Cells(r, 3).Value
        Exit Sub
      End If
    r = r + 1
    Loop
    Range("B21").Value = ""
End If
End Sub

working example in attached file

Best Regards

enter this formula in cell B21:

=IF(AND(B20<=1.9,B20>=1),"B","")

Originally Posted by mohd9876

enter this formula in cell B21:

=IF(AND(B20<=1.9,B20>=1),"B","")

mohd9876: there are more than one condition -> see into file ;-)

Best Regards

hi, haan, solution with formula without VBA

a bit simpler

Hi Guys,
Thanks. I've figured it out...
It can be as easy as vlookup(...., ,1)
use "1" instead of "0", then it can be within the range.

Thanks.

Regards,
Haan