Hi,
I've just created a vlookup UDF using the excel find function. I wanted to find a way the use vlookup to find columns BEFORE the column where the data is in (Vlookup only works to columns behind the data column).
I allready had a UDF for this, but it was extremely slow and volitile. This gives me more flexibility.
Here's the code:
[pre]
[/pre]
So, if you neglect the name SuperVlookup
, how can i improve this even more?
- input is the "to find" value as a range (lookupvalue)
- the range where the find looks (Note: ONLY the first column of the selection will be used should your selection span over more columns). (lookuprange)
- Foundcolumn is the final outcome, so 2 = the column RIGHT of the lookuprange and -2 would give the one LEFT of that one. (0 will be handled as if it was 1)
so up to this point exaclty the same as vlookup (other than being forced to look for a range, rather than a value)
the last 2 parameters are optional with the default settings as i prefer them:
- lookuppartial: if you want to match with a partial match, this has to be true, default is false
- lookupmatchcase: default = true, this one can be turned to false of the capital/small letters don't matter
Since i just plopped this code down as i was eating my sandwich: any improovements? flaws that i didn't count upon? ...
the VBA find code is tricky if you ask me, but it got the job done and i learned a few things
thanks!
I've just created a vlookup UDF using the excel find function. I wanted to find a way the use vlookup to find columns BEFORE the column where the data is in (Vlookup only works to columns behind the data column).
I allready had a UDF for this, but it was extremely slow and volitile. This gives me more flexibility.
Here's the code:
[pre]
Code:
Function SuperVlookup(LookUpValue As Range, _
LookUpRange As Range, _
FoundColumn As Integer, _
Optional LookUpPartial As Boolean = False, _
Optional LookUpMatchCase As Boolean = True)
Dim FoundRangeAddress As String
Dim FoundRange As Range
Set LookUpRange = LookUpRange.Columns(1)
On Error GoTo ErrMsg
If LookUpPartial = True Then
Set FoundRange = LookUpRange.Find(LookUpValue, , xlValues, xlPart, xlByRows, , LookUpMatchCase).Cells
Else
Set FoundRange = LookUpRange.Find(LookUpValue, , xlValues, xlWhole, xlByRows, , LookUpMatchCase).Cells
End If
If FoundColumn > 0 Then
SuperVlookup = FoundRange.Offset(0, FoundColumn - 1).Value
Else
If FoundColumn < 0 Then
SuperVlookup = FoundRange.Offset(0, FoundColumn + 1).Value
Else
SuperVlookup = FoundRange.Offset(0, FoundColumn).Value
End If
End If
GoTo endfunction
ErrMsg:
SuperVlookup = "#N/A"
endfunction:
End FunctionSo, if you neglect the name SuperVlookup
, how can i improve this even more?- input is the "to find" value as a range (lookupvalue)
- the range where the find looks (Note: ONLY the first column of the selection will be used should your selection span over more columns). (lookuprange)
- Foundcolumn is the final outcome, so 2 = the column RIGHT of the lookuprange and -2 would give the one LEFT of that one. (0 will be handled as if it was 1)
so up to this point exaclty the same as vlookup (other than being forced to look for a range, rather than a value)
the last 2 parameters are optional with the default settings as i prefer them:
- lookuppartial: if you want to match with a partial match, this has to be true, default is false
- lookupmatchcase: default = true, this one can be turned to false of the capital/small letters don't matter
Since i just plopped this code down as i was eating my sandwich: any improovements? flaws that i didn't count upon? ...
the VBA find code is tricky if you ask me, but it got the job done and i learned a few things
thanks!
