Lookup problems
 

Hola todos!
I want to do a double or triple Vlookup.
I want to do that by VBA. I use at this moment the program below.
just need to have the nearest value, because I have a approximate valu
for
LongLeader = 0.12456 'Exemple the program look in the list. It look
for Diameter, kind of tool and the length. But it will return th
length of the supplier. For the exemple, it will be 0.125 instead o
0.12456.

Set rng = [A1:A2000]
For Each cel In rng
If cel.Value = "Leader pin" Then
If cel.Offset(, 1).Value = DiaLeader Then
If cel.Offset(, 2).Value = LongLeader Then ' How can I program it t
look for the nearest value in cel.offset(,3).value
LongLeaderDME = cel.Offset(, 3).Value
End If
End If
End If

Next cel

thanks for help and if you have something different (anothe
programmation) it will be perfect to

--
Message posted from http://www.ExcelForum.com

Lookup problems
 

Try the code below: copy it all into a codemodule, and run TryNow.

HTH,
Bernie
MS Excel MVP

Dim LongLeaderDME As Double

Sub TryNow()
GetData 0.25, 3.475
MsgBox LongLeaderDME
End Sub

Sub GetData(DiaLeader As Double, _
LongLeader As Double)

Dim cel As Range
Dim rng As Range

Set rng = [A1:A2000]
For Each cel In rng
If cel.Value = "Leader pin" Then
If cel.Offset(, 1).Value = DiaLeader Then
If cel.Offset(, 2).Value LongLeader Then
LongLeaderDME = cel.Offset(, 3).Value
Exit For
End If
End If
End If

Next cel

End Sub

"zapatista66 " wrote in message
...
Kind diameter length Price
Leader pin 0.250 3.250 3$
Leader pin 0.250 3.750 4$
Leader pin 0.250 4.250 10$
Leader pin 0.750 4.750 15$
Leader pin 0.750 5.250 5$
Leader pin 0.750 5.750 7$
Socket 1 6.250 5$
Socket 1 6.750 2$
Socket 1 7.250 21$

LongLeaderDME = length
So, I want to look first in kind, after in diameter and to return the
length in the list.
exemple: kind:Leader pin, diameter:0.250, and LongLeader:3.743

The program will return the line 2. Length=3.750 because is the nearest
value (up) for LongLeader. Finally, I can have the price 4$.

Is it possible ?


---
Message posted from http://www.ExcelForum.com/

Lookup problems
 

I have to sort my data. If not, the nearest value will be false.

can I use something without sorting my data?

I don't know why but it takes my last data. The program start at th
end of range A1:A2000???

--
Message posted from http://www.ExcelForum.com