Table lookup UDF

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
'                          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.
R/CCD
4MonthSales
5Jan$8,141
6Feb$2,647
7Mar$5,211
8Apr$4,825
9May$7,225
10Jun$9,031

<tbody>
</tbody>

And here's the UDF in action.

R/CKLMN
4MonthSalesFormulaComments
5may$7,225L5: =mylookup(K5,C5:C10,D5:D10)Lower case
6FEB$2,647L6: =mylookup(K6,C6:C11,D6:D11)Upper case
7Apr$4,825L7: =mylookup(K7,C7:C12,D7:D12)Mixed case
8jum#VALUE!L8: =mylookup(K8,C8:C13,D8:D13)Misspelled
9Mar#VALUE!L9: =mylookup(K9,C9:C14,D9:D14)Trailing space
10Jul#VALUE!L10: =mylookup(K10,C10:C15,D10:D15)Missing value

<tbody>
</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Why are you using an UDF? Is possible to achieve what you need with INDEX/MATCH.

L5 copied down
=INDEX(D$5:D$10,MATCH(K5,C$5:C$10,0))

M.
 
Upvote 0
Why are you using an UDF? Is possible to achieve what you need with INDEX/MATCH.

L5 copied down
=INDEX(D$5:D$10,MATCH(K5,C$5:C$10,0))

M.
Hmmm... You're right. It appears to do exactly the same thing. When I wrote the UDF, I was under the impression that either Index or Match required the values to be sorted. Apparently, I was confusing that with Lookup, which has that requirement.

Thanks for pointing that out.
 
Upvote 0
No UDF is required. This formula works.
In M5 then copied down

=IFERROR(VLOOKUP(K5,$C$5:$D$10,2,0),"")
Double Hmmm... That appears to work as well. It looks like I did a lot of work for nothing. (sigh)

Marcelo's version and my UDF both work when the ranges are either vertical or horizontal or one of each. For this version, I would need to switch to HLookup if they are both horizontal. And I don't think there a way to have one of each, not that that would ever come up.

So other than that minor difference, what are the advantages of each method?
 
Upvote 0
Here's what I think are some tradeoffs.

The VLookup syntax is slightly simpler. This is because it only calls one function and because, since it uses an offset for the results table, rather than requiring that the full range be spelled out. But this has two drawbacks:

  1. The two vectors must be aligned (start and end on the same row). That is, it cannot work if one vector is C5:C10 and the other is D15:D20.
  2. The offset cannot be negative, so the result vector must be to the right of the reference vector.
I think both of these drawbacks are relatively rare, so I am inclined to use the VLookup method unless I encounter a situation that presents one of these restrictions. Or someone points out another drawback.

Comments?
 
Upvote 0
Try this formula

=IFERROR(INDEX($D$5:$D$10,MATCH(K5,$C$5:$C$10,0)),"")

Both points 1 & 2 are Looked after by this formula.
Index range Can be D15:D20.
 
Last edited:
Upvote 0
Try this formula

=IFERROR(INDEX($D$5:$D$10,MATCH(K5,$C$5:$C$10,0)),"")

Both points 1 & 2 are Looked after by this formula.
Index range Can be D15:D20.

This is the method suggested by Marcelo, no? (Except for the IFERROR.)

Yours is slightly simpler. His is slightly more general.
 
Upvote 0
I just realized one more relatively minor restriction to the VLookup method. The reference column must be the first column in the table.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top