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/#!topic/microsoft.public.excel/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$1 2,0)),INDEX(Sheet2!$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