I starting to get the concept of how Index and Match are supposed to work, but confused as to why this formula does not work.
I have 4 user sheets with tables in use, combining data from 3 into one. This is my current setup, which is working well so far.
Sheet1 "tblClickers" (export from phishing report):
Column A = Email
Column B/C = First/Last Name
Column N = phish failure data
Sheet2 "TblAD_US" (AD Export):
Column A = Title
Column C = Name
Column E = Email
Column F = Department #
Sheet3 "TblComb": combination sheet listing relevant data from other three.
Column A = "=tblAD_US[title]"
Column B = "=tblAD_US[Name]"
Column D = "=tblAD_US[EmailAddress]"
Column E = "=tblAD_US[Department]"
Column K = "=INDEX(tblClickers[Failures],MATCH([@[Email Address]],tblClickers[Email Address],0))"
Sheet4 "TblAD_Depts" (report listing department heads):
- column c = Department #
- Column D = Department head
I now want another column in Sheet 3 that lists the matching department head (sheet4/columnD) associated with the departments of each user. The data point I will match on is the Department# listed in Sheet 2/3 and Sheet 4.
I tried using the same formula syntax as I did for Clickers, but I'm getting a #N/A error.
=INDEX(TblAD_Depts[Department Head],MATCH([@Department],Tbl_Am_Depts[Department],0))
Where am I getting mixed up here? Do I need to reference the original data source for "Department#" in sheet 2 or can I "reference the reference" in sheet3?
tl;dr, want a new column L in Sheet 3. Values returned will be column D from Sheet 4, using Column E in sheet 3 and Column C in sheet 4 to match on.