Help with lookup

[Adrev]

Dear all
I need to search several folders for a match on names taken from a namelist, and then display the department name of the folde the name was found in.
Namelist[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Department
[/TD]
[/TR]
[TR]
[TD]Tina
[/TD]
[/TR]
[TR]
[TD]Kim
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lars
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bo
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 (value listed in A1 is department)
[TABLE="width: 500"]
<tbody>[TR]
[TD]JUR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tina
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lars
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2 (value listed in A1 is department)[TABLE="width: 500"]
<tbody>[TR]
[TD]FIN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Kim
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Bo
[/TD]
[/TR]
</tbody>[/TABLE]

I have tried Vlookup and search, but can't get them to work.

Please help me
Thank you for takeing the time

 

[Andrew Poulsom]

Formula in B2 copied down (note the named range):

Excel 2010

	A	B	C	D
Name	Department	Sheet1
Tina	JUR	Sheet2
Kim	FIN
Lars	JUR
Bo	FIN

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Namelist​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]B2[/TH]
[TD="align: left"]{=INDIRECT("'"&INDEX(Sheets,MATCH(1,COUNTIF(INDIRECT("'"&Sheets&"'!B:B"),A2),FALSE))&"'!A1")}[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]Sheets[/TH]
[TD="align: left"]=Namelist!$D$1:$D$2[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Adrev]

Thanks for the fast respond.
I tryed to follow your formula, because i work on a danish excel i had to change the , to ; but other then that it stayed the same.
It didn't work on my document, so i tryed to create a test document to see if i could make it work there. But i still get a #NAME? any idea why that might be?

Thanks for the help

 

[Andrew Poulsom]

Did you list the sheet names in D1:D2 and insert the name Sheets that referred to it?