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

    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
    Location
    Aberdeen, Scotland
    Posts
    1,067

    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

    Code:
    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)
                Loop
                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
       
    NotNumeric:
       TestBool = False
       TestVal = Right(TestVal, Len(TestVal) - 1)
       Resume
    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

    =FindLower(S2,1)

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

    Hope this helps somewhat

    David

  3. #3
    Join Date
    Jun 2002
    Location
    Houston, TX
    Posts
    116
    I thought we answered this in your previous post.
    http://www.dbforums.com/t988059.html

    ???texasalynn

  4. #4
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    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 22: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
  •