Search for a column across multiple sheets and copy them in 1st sheet.

Hi,
I have a work book with sheet 1 containing ID numbers in column A. Remaining sheets have 3 columns ID no, name and Age. Each sheet corresponds to a different department and is named based on the department. I have attached an example file. What i want is to search for each ID in the column A of 1st sheet
in all other sheets and copy the corresponding Age. it would be neat to have the department name on top of the corresponding column in sheet 1. please refer to the example sheet/

If I'm understanding you correctly, you can do this with a formula. Use the formula below in C2, fill right and fill down:

=IFERROR(VLOOKUP($A2,INDIRECT(C$1&"!$A$2:$C$5",TRUE),3,FALSE),0)

Taking a second look, you should expand the range for your bigger sheets. Something like:

=IFERROR(VLOOKUP($A2,INDIRECT(C$1&"!$A$2:$C$30",TRUE),3,FALSE),0)

thanks for your reply...so I used this and everything is marked 0 in the sheet..my data is in different sheets..i havent used INDIRECT before so dont know how it works to pin point the exact issue with formula

INDIRECT allows you to use a variable to apply different sheet names. If the header in your columns exactly matches the sheet name, the formula should perform the lookup on the sheet specified by the header. If you look at my attachment in post 3, the header in row 1 tells INDIRECT which sheet to look at. If you insert a space in C1, turning it into "Department 1" instead of "Department1", then the lookup fails and you get zeroes. Double-check your sheets to be sure that the headers on your lookup sheet exactly match the sheet names.

ah..i understand..thanks for your explanation..but i dont have the column headers already..in sheet 1 all i have is column A. that is it

So... it'll work then? Just make sure that when you develop the rest of your actual sheet1, the names of your department sheets are in sheet1 cells C1, D1, E1, etc.

it should..but there are like 250 sheets..and their names are random..i just put department1 and 2 for example...thats why i wanted a macro to do this

so i copied sheet names but the formula still doesnt work

Am getting #REF! on the cell if i donot suppress the error, somehow it is not picking up the reference..

It's hard for me to diagnose further without seeing a sample that shows what you're seeing, since it works on your initial sample. If you'd prefer a VBA solution anyway, though, try the following:

Sub SheetLookup()
Dim LR As Long
Dim LC As Long
Dim i As Long
Dim j As Long
Dim Master As String
Dim LkSht As String


Master = "Sheet1"  'Replace Sheet1 with name of master sheet

Application.ScreenUpdating = False
LR = Sheets(Master).Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets(Master).Cells(1, Columns.Count).End(xlToLeft).Column
For j = 3 To LC
    LkSht = Sheets(Master).Cells(1, j).Value
    For i = 2 To LR
        Sheets(Master).Cells(i, j).Formula = "=IFERROR(VLOOKUP(" & Range("A" & i) & ",'" & LkSht & "'!A2:C" & LR & ",3,FALSE),0)"
    Next i
Next j
Application.ScreenUpdating = True
End Sub

Keep in mind, though, that the headers in row 1 will still need to exactly match a sheet name.

Maybe this version will work better? The variation below will take the names of the sheets and turn them into your column headers, so you don't need any headers in row 1. The procedure will create the headers based on the sheet names, guaranteeing that they'll match:

Sub SheetLookup()
Dim LR As Long
Dim LC As Long
Dim i As Long
Dim j As Long
Dim Master As String
Dim LkSht As String


Master = "Sheet1"  'Replace Sheet1 with name of master sheet

Application.ScreenUpdating = False
LR = Sheets(Master).Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets.Count
For j = 2 To LC
    Sheets(Master).Cells(1, j + 1).Value = Sheets(j).Name
    LkSht = Sheets(Master).Cells(1, j + 1).Value
    For i = 2 To LR
        Sheets(Master).Cells(i, j + 1).Formula = "=IFERROR(VLOOKUP(" & Range("A" & i) & ",'" & LkSht & "'!A2:C" & LR & ",3,FALSE),0)"
    Next i
Next j
Application.ScreenUpdating = True
End Sub

thanks so much for your patience and help!! so i took out the ISerror part, and ran this code, i got #NAME? error on 1st 5 lines and then application defined or object defined error vba

Sheets(Master).Cells(i, j + 1).Formula = "=IFERROR(VLOOKUP(" & Range("A" & i) & ",'" & LkSht & "'!A2:C" & LR & ",3,FALSE),0)"

ok..i think i figured out the issue, the values in column A if it is numeric the code is working, if its not it is not working.

see the image below for Capture.PNG

Cstr doesnt help

this is great! working perfect..u sir are awesome!!

Thanks so much for your help!