Lookup Data From Multiple Sheets

[mahmoud-lee]

I want to use the function to search for the employee code of Sheet No. 1 to No. 3

[alansidman]

This link should give you a clear example of what you need to do to make it work.

https://groups.google.com/forum/#!to...el/UeAYuh8LAN4

[bakerman]

Or VBA solution.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    If Target.Address = "$D$5" Then
        For j = 1 To 3
            sn = Sheets(j).Columns(1).Find(Target.Value, , xlValues, xlWhole).Resize(, 2)
            If Err = 0 Then
                y = y + 1: Range("E5") = sn(1, 2)
                Exit For
            End If
            Err.Clear
        Next
        If y = 0 Then Range("E5").ClearContents: MsgBox "Employee name not found", vbInformation, "Sorry"
    End If
   
End Sub

[mahmoud-lee]

Thank you very much on this link Mr. Alancisdman
Thank you very much, Mr Bakerman on this code
But you can solve this by formula (Index)
I hope so, if you could? The best solution by the formula
I am using Office 2003
Thanks again for help

[bakerman]

On Sheet4 make a named range (A1 to A3) called MySheets containing Sheet1, Sheet2, Sheet3.
In Sheet4!E5 put next formula. Remember that this is an Array-formula. (Ctrl-Shift-Enter)

=VLOOKUP(D5,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A20"),D5)>0),0))&"'!A2:B20"),2,0) 

[Admin]

Hi

If there are not too much sheets, you could also try this one.

=LOOKUP("zzzz",CHOOSE({1,2,3,4},"",INDEX(Sheet3!$B $2:$B$12,MATCH(D5,Sheet3!$A$2:$A$12,0)),INDEX(Shee t2!$B$2:$B$12,MATCH(D5,Sheet2!$A$2:$A$12,0)),INDEX (Sheet1!$B$2:$B$12,MATCH(D5,Sheet1!$A$2:$A$12,0))) )

[mahmoud-lee]

very very good
Thank you all