Excel Index Match syntax question. getting a #N/A

1
votes

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.

1
excelexcel-formulamatch
Usually that means, no match found. – JvdV
okay, but I know that Column E in Sheet 3 is a list of Department numbers. 1234, 1235, 1236, etc. I also know that Column C in Sheet 4 has a list of Department Numbers. 1235, 1236, 1237. I can't be getting N/A on every single one. There should be matches. – Jacob K
For example: Are you comparing numbers to numbers that look like numbers but are actually text values? Things like that would throw off the match. – JvdV
hmm... I didn't think to try that. I'll check the formatting. I tried this formula as well "=INDEX(Tbl_Am_Depts[Department Head],MATCH(tbl_AD_US[Department],Tbl_Am_Depts[Department],0))" but it doesn't let me complete it. says that something is missing. – Jacob K
No dice. They were all formatted as General. I changed them all to Number and it didn't make a difference. – Jacob K

1 Answers

1
votes

Okay, (@JvdV) had it figured out. Even though I selected each column for "Department" and changed the format to [Number], one column was still listed incorrectly.

For some reason, Column C in Sheet 4 had each "Number Stored as Text". I'll just do a [Text to Column] and call it a day!

Thanks!

Convert to Number