Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Unanswered: Vlookup Range of values

    Is it possible to use a vlookup to find a find a value against a range of values.

    Ex. If a car has 12,234 miles on it and you have a table that looks like this <=8000 +3%
    <=16000 +4%
    <=24000 +5%

    Will a vlookup return the +3% value?

    =IF(H6=1999,VLOOKUP(S6,MVP!$B$52:$C$58,2,FALSE),IF (H6=2000,VLOOKUP(S6,MVP!$B$59:$C$65,2,FALSE),IF(H6 =2001,VLOOKUP(S6,MVP!$B$66:$C$72,2,FALSE),"N/A")))

    that is what I have so far. THe B column is = to the miles and the C column is = to the %.

    S Column is where the miles are at.

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland

    Re: Vlookup Range of values

    I dont think you can but you could Try this

    Input this code into a blank module of your Worksheet

    Function FindLower(ByVal ThisCell As Range, ByVal ColAccross As String)
        Dim Lastrow As Single
        Dim I As Integer
        Dim TestVal As String, Testdbl As Double, retval As String
        Dim TestBool As Boolean
        Dim RangeList(6) As Integer
        'set value of Lastrow
        Lastrow = Range("B65000").End(xlUp).Row
        TestBool = False
        For I = 52 To 58
            TestVal = Range("B" & I).Value
                Do Until TestBool = True
                    'check to see if you can change a value to a Double
                    On Error GoTo NotNumeric
                    TestBool = True
                    Testdbl = CDbl(TestVal)
                TestBool = False
            RangeList(I - 51) = Testdbl
        Next I
        'switch error handling off
        On Error GoTo 0
        'find first lower than
        For I = 1 To Lastrow - 1
            If ThisCell.Value <= RangeList(I) Then
                retval = Range("B" & I + 51).Offset(0, ColAccross).Formula
                FindLower = retval
                Exit For
            End If
        Next I
       Exit Function
       TestBool = False
       TestVal = Right(TestVal, Len(TestVal) - 1)
    End Function
    This looks takes a cell value and checks if is lower than the cell in
    Column B.

    After you have the code in the module you can then type in to your workbook


    Hope this works for you it is also set up for the specific case you were talking about

    Hope this helps somewhat


  3. #3
    Join Date
    Jun 2002
    Houston, TX
    I thought we answered this in your previous post.


  4. #4
    Join Date
    Mar 2004
    Fort Worth, Texas, USA
    Step 1: Create a new lookup table that looks like:

    2000000000 3%
    2000008000 4%
    2000016000 5%
    2001000000 x%
    2001008000 y%
    2001016000 z%

    then use VLOOKUP(TEXT(yearcell,"0000") & TEXT(milescell,"000000"), lookuptableaddress, 2, TRUE). Make sure the first column is text by preceding with a single quote '
    Last edited by actuary; 03-17-04 at 21:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts