Using vLookup in UDF

I am working on a UDF that needs to use vlookup within it. I'm having problems using a VBA variable within the vlookup function. The end goal is to have a vLookup type function that looks up each element in the cell. For example:

File 1:
Column1
1
2
3,4,5
6

File 2:
Column1-Column2
1-a
2-b
3-c
4-d
5-e
6-f

I want the file output to be
Column1- Column2
1-a
2-b
3,4,5-c,d,e
6-f

I'm trying to work on a UDF to do this but am running into a problem. Here is what I have so far (UDF is only half done but I'm stuck):

Please Login or Register  to view this content.

When I have the vlookup formula try to access the array variable by either Application.WorksheetFunction.VLookup(v, ReferenceTable, ColumnIndexNumber, False) or Application.WorksheetFunction.VLookup(LookupCellArray(i), ReferenceTable, ColumnIndexNumber, False), nothing is found. If I modify this to Application.WorksheetFunction.VLookup(LookupCell, ReferenceTable, ColumnIndexNumber, False) it works. Since I need to look up each element in the cell, I need to feed the vlookup formula a VBA variable and not just the LookupCell value. Why isn't that working? In the msgbox both v and LookupCell appear to be the same so I'm wondering if this is a formatting issue or non-printing character issue?

ProtonLeah- that is an interesting want to solve the problem. I might try that.
Bakerman2- that works, although I do need it flexible enough to work where the lookup values are not just numbers. I'm confused on why setting v to an integer instead of a string works. I'd like this as flexible enough to work like the normal v lookup where you can lookup:
1
2,3
a
a,b
test1
test2

I tried making v a variant but then it gives the generic #value error.

Thanks bakerman2. I modified it some to account for values that aren't found, but it is working pretty well now.

Please Login or Register  to view this content.