VLOOKUP over multiple sheets

[Yusuf]

Hi
Is it possible to write a VLOOKUP formula that searches for a result over multiple sheets in the same workbook?

 

[Jonmo1]

I've been working on that...

This UDF is probably extremely ineficient, but it works so far...
You only have to use 2 areguments in this function. But there are 3 optional arguments.

=myvlookup(lookupvalue,range,"sheetname","sheetname","sheetname")

the 3 sheetnames are optional, if omitted, it searches the sheet the function is entered on.

I've only written it for 3 sheets so far. You can add to it if you like.

I purposely left out the ColumnIndex in my vlookup. it will default to the # of columns in the range you specify. so if you look in A:C - it will be 3.

Public Function myvlookup(myvalue As String, myrange As Range, Optional a As String, Optional b As String, Optional c As String)
Dim myarray()
x = myrange.Column
Y = x + myrange.Columns.Count - 1


sarray = Array(a, b, c)
For i = 0 To WorksheetFunction.CountA(sarray) - 1
    If sarray(i) <> "" Then Count = Count + 1
Next i
Count = Count - 1

If Count < 0 Then
    Count = 0
    sarray = Array(myrange.Parent.Name)
End If

For i = 0 To Count
    LR = LR + Sheets(sarray(i)).Cells(Rows.Count, x).End(xlUp).Row
Next i

ReDim myarray(2, LR)


For i = 0 To Count
    LR = Sheets(sarray(i)).Cells(Rows.Count, x).End(xlUp).Row
    For j = 1 To LR
        myarray(0, j + RowCount) = Sheets(sarray(i)).Cells(j, x).Value
        myarray(1, j + RowCount) = Sheets(sarray(i)).Cells(j, Y).Value
    Next j
    RowCount = RowCount + LR
Next i

For Z = 0 To RowCount
    With WorksheetFunction
    If .Proper(myarray(0, Z)) = .Proper(myvalue) Then q = myarray(1, Z)
    End With
Next Z

myvlookup = q
End Function

 

[texasalynn]

take a look at the MORFUNC add-in

http://www.mrexcel.com/board2/viewtopic.php?t=131224