Problem with formula that is supposed to lookup and return multiple values

[ptg]

I'm trying to pull (look up) employee names that are working on a specified project. Multiple employees work on a specified project and employees work on multiple projects. The simple lookup formula does not work since it only returns the first employee name from a specified project.

I did try an index array formula included below, that almost works except for some reason it returns 3 extra employee names (Fred, Sara, and Jan) and is missing 2 employees (Jim and Mary). See the attached file.

{=INDEX($G$5:$H$43,SMALL(IF($G$5:$G$43=$D$6,ROW($G$5:$G$43)),ROW(1:1)),2)}

Also, the formula returns duplicate names which I don't want. I only want the name shown once for that particular project.

Thanks.

[windknife]

Try this

C10

Please Login or Register  to view this content.

Commited with Ctrl+Shift+Enter.

You can also see attachment.


Hope this helps,
windknife

[DonkeyOte]

Attached are a couple of slightly different approaches, namely

1 - Pivot Table (preferred)

2 - Index Key Based Approach (Green cells) which negate need for expensive Array formulae, undoubtedly less elegant but at the same time undoubtedly more efficient.

[ptg]

Thank you Windknife and DonkeyOte, each of your methods work. I appreciate your help.

[ptg]

Windknife,

I ended up using your solution. Can you explain how that formula works, so that I can understand it for future reference?

Thanks.