Find multiple values that match and return value from another column, in another workbook

Question

I'm trying to create a super VLOOKUP function in VBA and I've gotten it to work fine within the same sheet but i can't seem to figure it out for getting it to work across other books.

Basically this function takes three arguments: Lookup Value, Lookup Table and Return Column.

Lookup value is used to match against every cell within the lookup table and when a match is found, it will return the value from the cell in Return Column at the matched cells row.

Here is my code:

Function SUPERLOOKUP(lv As Range, lt As Range, rc As Range)
Dim cell As Range
Dim output As String

For Each cell In lt
    If cell.Value = lv Then
        output = output & Range(ColumnLetter(uRC.Column) & cell.Row).Value & ", "
    End If
Next cell

SUPERLOOKUP = Left(output, Len(output) - 2)
End Function

ColumnLetter is a function i use to convert the column number to it's letter value.

Any help is appreciated or even to be pointed in the right direction.

user Return Column, thats what it was previously called (i guess it would be rc instead in that code. — malcojus

Tim Williams Tim Williams · Accepted Answer · 2015-04-10T17:52:13

In this line:

output = output & Range(ColumnLetter(uRC.Column) & cell.Row).Value & ", "

Range will reference the ActiveSheet, so you need to qualify it with a worksheet reference.

Eg (switching to Cells to avoid having to convert a column number to a letter):

output = output & lt.Parent.Cells(cell.row, uRC.Column).Value & ", "

or

output = output & cell.EntireRow.Cells(uRC.Column).Value & ", "

Find multiple values that match and return value from another column, in another workbook

1 Answers