Index Match Between 3 Sheets

[ryguy7272]

I have a list if Employee ID numbers on my sheet named ‘Filtered List’. I’m
trying to use each of these ID numbers to look up employees on a sheet named
‘Master’. From ‘Master’, I want to find the name that is in the same row as
the ID, but in Column C. Then, and finally, I want to take this result, and
match this name to a name in Column B in a sheet named ‘Goals’ and pick up
the corresponding value that is in the same row.

I’m trying this function:
=INDEX(Goals!$C$1:$C$250,MATCH(INDEX(Master!$C$1:$C$180,MATCH('Filtered
List'!A60,Master!$A$1:$A$180,0)),Master!$C$1:$C$180,0))

The results are kind of weird. I can’t explain it. I thought I would get a
value which is on row 112, but I actually get a value which is on row 17, and
this result seems completely unrelated to what I wanted. However, over time,
I’ve come to realize that if something is incorrect, it is me, and not Excel.
If someone can please point out my error in logic, please post back.

Thanks,
Ryan---

 

[Pete_UK]

Well, I can't see any reference to column B on the Goals sheet.

Pete

 

[ryguy7272]

Thanks Pete, but that's not it. I think I see what's happening...The ID may
show up a few times, and I think the function is only picking up the first
match, but no subsequent matches. How can I get around this? I may have up
to five IDs, in the 'Master' sheet.

Thanks,
Ryan--

 

[ryguy7272]

What I ultimately want to do is 'sumproduct' the values in the 'Goals' sheet.
If I use this function:
=SUMPRODUCT(--(Goals!$A$1:$A$245=A60),(Goals!$C$1:$C$245))

I get the goals for one single individual. The problem is that this
individual is linked, or tied to, two other individuals, and this simple
sumproduct function is causing me to miss the goals of the other individuals.
Therefore, I somehow have to identify the other two people that the first
person is linked to.

Does that make sense? Does anyone have any ideas about this? I may very
well be over complicating the issue, so if someone has an idea of how I may
simplify this, I am definitely open to suggestions?

Thanks,
Ryan---