Index Match not returning correct values

I am attempting to create a xls that allows the user to input a Emp Id under the correct Manager category that will lookup the Manager name for another worksheet.  What am I missing.  I have included a sample file.

Data extract sheet the user inputs the ID and return the name, except my version is not returning the correct manager name, except for col D:E.

What am I doing wrong:

=IFERROR(INDEX('EMP LIST'!$A:$A,MATCH(1,'EMP LIST'!H2,0)),'EMP LIST'!$B:$B)

Open in new window

kfsSample.xlsx
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
So If I entered, say, 132565 in column A  you would expect to see ALLEN, JEFFERY in column B?

If that's the case this INDEX/MATCH formula, in B2 and copied down, would work, though it could also be done with VLOOKUP.

=IFERROR(INDEX('EMP LIST'!$B:$AB,MATCH(A2,'EMP LIST'!$A:$A,0),1),"")

That formula can also be copied to columns E and G to get the manager name for the SR_MANAGER_ID and DIRECTOR_ID.
0
 
NorieVBA ExpertCommented:
In every one of the formulas INDEX('EMP LIST'!$A:$A,MATCH(1,'EMP LIST'!H2,0)) returns an error so the formula actually returns the value from 'EMP LIST'$A:$A that corresponds to the row the formula is in.

What IDs/values are you actually trying to lookup?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
If the user enters the value (ID) in Col A ManagerID I want the correlated name displayed in Col b and the same for each of the different ID columns.SR_MANAGER_ID col(D)
Col A ManagerID , should return Col(B) - Manager Name
Col(d) - Sr_Manager_ID, should return Col(E) - Sr Manager Name
,DIRECTOR_ID (col F) should return Col(g) -  Director Name
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the great assist, that did the trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.