In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman