# Thread: Vlookup Range of values

1. Registered User
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. Registered User
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. Registered User
Join Date
Jun 2002
Location
Houston, TX
Posts
116
http://www.dbforums.com/t988059.html

???texasalynn

4. Registered User
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
•