Lookup problems

[zapatista66]

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

 

[Bernie Deitrick]

You might want to post a representative sample of your data table, what you
are looking for, and what you would expect to have returned.

HTH,
Bernie
MS Excel MVP

 

[zapatista66]

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 th
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 neares
value (up) for LongLeader. Finally, I can have the price 4$.

Is it possible

 

[Bernie Deitrick]

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]

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???