Lookup a value across sheets

K

KSpider

I have a master sheet of workers names, the names are in the A column.
in the B colm i want a formula that will go thru department sheets an
find the name in A, and return the value in c1 on that sheet whic
contains the department name.

Master Sheet
cell a2 Joe Smith
cell a3 (returns the word HR)

HR sheet
cell a12 Joe Smith
Cell c1 HR

RD Sheet
cell a2 Allie Jones
cell c1 RD

Does this make sense?

I trying to use Index and a function called Threed to no avail
Thanks,
Spide
 
M

Myrna Larson

Is ThreeD a UDF written in VBA? If so, can you post it?

You may need VBA for this. Many worksheet functions can't be written as 3D
formulas. I have included below a somewhat generic function. It searches
column 1 on ALL worksheets EXCEPT the sheet that contains the formula. When
there's a match, it returns the value from cell C1 on that sheet.

Function DeptName(EmployeeName As String) As String
Dim i As Long
Dim x As Variant
Dim SkipSheet As String

SkipSheet = Application.Caller.Parent.Name

For i = 1 To ThisWorkbook.Worksheets.Count
With Worksheets(i)
If .Name <> SkipSheet Then
x = Application.Match(EmployeeName, .Columns(1), 0)
If IsNumeric(x) Then
DeptName = .Range("C1").Value
Exit Function
End If
End If
End With
Next i
DeptName = "Not found!"
End Function
 
F

Frank Kabel

Hi
you may post the formula you have tried. Also it would be easier if you
duplicate the department name in column C for all rows
 
J

John

I use the vlookup formula for this all the time, but I
have never tried crafting it to then check multiple
sheets.
 
U

unlikeKansas

=IF(ISNA(MATCH(A6,HR!A$1:A$100,0)),IF(ISNA(MATCH(A6,RD!A$1:A$100,0)),IF(ISNA(MATCH(A6,Sales!A$1:A$100,0)),"No Such Person In Any Department",Sales!C$1),RD!C$1),HR!C$1)

Works for 3 departments (i.e. HR, Sales & RD) and by extension would work for up to 7 departments, but after that, because of the limit of 7 nested if statements, it will not work.

unlikeKansas
 
A

Anthony Slater

I use the following formula to look accross various sheets
and it works fine for me.

If(iserror(vlookup(a2,HRsheet!$A$1:$C$9999,3,0))=FALSE,
(vlookup(a2,HRsheet!$A$1:$C$9999,3,0)),IF(iserror(vlookup
(a2,RDsheet!$A$1:$C$9999,3,0))=FALSE,(vlookup(a2,RDsheet!
$A$1:$C$9999,3,0)))

Adjust the ranges to suit as neccessary

Hope this helps.
 
H

hgrove

Frank Kabel wrote...
...
How do you often say: A quibble:
would assume your formula errors out if the sheet name
contains spaces, etc. so I'd propose:
Click to expand...


When using a list of worksheet names, who's to say that the names i
that list don't contain enclosing single quotes?
=INDEX(T(INDIRECT("'! & T(OFFSET(WSList,INT(seq3D/MEPD),
Click to expand...
...

If you're gonna quibble, don't make typos! That > "'! & < is a synta
error. You meant > "'" & <
 
Top