Table lookup UDF

[JenniferMurphy]

I was having trouble getting the Match and Lookup function to work, so I put them in a UDF. It has a simple syntax and does not require that the lookup list be sorted. Here's the code. Please let me know if there is a better way.

'                          My LookUp Function

' Look up (search for) a value in one list and return the corresponding value in another.
' The lists must be one-dimensional (vectors), but either one can be vertical or horizontal.
' The comparison is not case sensitive ("abc" = "ABC" = "aBc"),
'   but it does respect spaces ("abc" <> "a bc" <> " abc" <> "abc ").

' This function does what the built-in LookUp function should have done.
' It works the same way, but does not require that LookupVector be sorted.

' Syntax: =MyLookUp(LookupValue, LookupVector, ResultVector)

'   LookupValue   The value to be looked up in the Lookup vector.
'   LookupVector  The vector (row or column) where the LookupValue will be searched for.
'   ResultVector  The vector where the corresponding value will be found and returned.

Function MyLookUp(LookupValue, LookupVector, ResultVector)
Dim LookupIndex
LookupIndex = WorksheetFunction.Match(LookupValue, LookupVector, False) 'Find the first match
MyLookUp = WorksheetFunction.index(ResultVector, LookupIndex)           'Find the corresponding value
End Function

Here's a simple table.
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Sales[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: right"]$8,141[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: right"]$2,647[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: right"]$5,211[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: right"]$4,825[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]May[/TD]
[TD="align: right"]$7,225[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Jun[/TD]
[TD="align: right"]$9,031[/TD]
[/TR]
</tbody>[/TABLE]

And here's the UDF in action.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Comments[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]may[/TD]
[TD="align: right"]$7,225[/TD]
[TD]L5: =mylookup(K5,C5:C10,D5:D10)[/TD]
[TD]Lower case[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]FEB[/TD]
[TD="align: right"]$2,647[/TD]
[TD]L6: =mylookup(K6,C6:C11,D6:D11)[/TD]
[TD]Upper case[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: right"]$4,825[/TD]
[TD]L7: =mylookup(K7,C7:C12,D7:D12)[/TD]
[TD]Mixed case[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]jum[/TD]
[TD="align: right"]#VALUE![/TD]
[TD]L8: =mylookup(K8,C8:C13,D8:D13)[/TD]
[TD]Misspelled[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: right"]#VALUE![/TD]
[TD]L9: =mylookup(K9,C9:C14,D9:D14)[/TD]
[TD]Trailing space[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Jul[/TD]
[TD="align: right"]#VALUE![/TD]
[TD]L10: =mylookup(K10,C10:C15,D10:D15)[/TD]
[TD]Missing value[/TD]
[/TR]
</tbody>[/TABLE]

 

[JenniferMurphy]

It is actually possible to make VLOOKUP more flexible using CHOOSE, but there's really no point when INDEX/MATCH already achieves the same thing and is less confusing.

I'll probably use VLOOKUP (and HLOOKUP) unless I run into one of its limitations, which is highly unlikely:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"]Likelihood[/TD]
[TD="align: center"]Restriction[/TD]
[/TR]
[TR]
[TD="align: center"]Unlikely[/TD]
[TD]Reference column not column 1[/TD]
[/TR]
[TR]
[TD="align: center"]Unlikely[/TD]
[TD]Value column to the right of the reference column[/TD]
[/TR]
[TR]
[TD="align: center"]Highly unlikely[/TD]
[TD]Value and reference columns do not start on the same row[/TD]
[/TR]
[TR]
[TD="align: center"]Extremely unlikely[/TD]
[TD]Value and reference columns are not the same orientation (both vertical or both horizontal)[/TD]
[/TR]
</tbody>[/TABLE]

In any case, I don't need my UDF. That's a couple of hours of my life I'll never get back. (Probably more than a couple.)

 

[Marcelo Branco]

Jennifer,

I think you should take a look at
http://www.exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html

M.

 

[JenniferMurphy]

Jennifer,

I think you should take a look at
http://www.exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html

M.

Wow! Nice article. Thanks.