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.

R/C	C	D
4	Month	Sales
5	Jan	$8,141
6	Feb	$2,647
7	Mar	$5,211
8	Apr	$4,825
9	May	$7,225
10	Jun	$9,031

<tbody>
</tbody>

And here's the UDF in action.

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

<tbody>
</tbody>

 

[Marcelo Branco]

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.

 

[kvsrinivasamurthy]

No UDF is required. This formula works.
In M5 then copied down

=IFERROR(VLOOKUP(K5,$C$5:$D$10,2,0),"")

 

[JenniferMurphy]

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.

 

[JenniferMurphy]

ADVERTISEMENT

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?

 

[JenniferMurphy]

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?

 

[kvsrinivasamurthy]

ADVERTISEMENT

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.

 

[JenniferMurphy]

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.

 

[JenniferMurphy]

I just realized one more relatively minor restriction to the VLookup method. The reference column must be the first column in the table.

 

[RoryA]

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.