List employee name based on multiple criteria - how?

**Kagesen** · 05-26-2016, 06:30 AM

Hi all,

I have an Excel sheet with data, as seen in attached file.

I need a formula that can list the "Personnel Name" (column D) based on the "Cost Center Name" (column C). I.e. if I select the Cost Center Name for "Technology department" then a list with Personnel Names from that department is populated. The Personnel Name must only be listed once. How can I do that?

I tried searching for a solution but cannot find a relevant one. Some people mention INDEX + MATCH others VLOOKUP or SUMIFS....but I cannot get that working.

Thank you in advance for your help.

**avendi** · 05-26-2016, 07:21 AM

Hey Kagesan. This website should help you do this:
http://www.globaliconnect.com/excel/...=77&Itemid=473
n.b. make sure you pay heed to the part about using an array formula.
Cheers

**JohnTopley** · 05-26-2016, 07:59 AM

I produced a list of employees using the "Data" ==>"Remove Duplicates" (See Sheet1).

Copied this to column L and then matched employees vs Cost Center

=INDEX($C$2:$C$5000,MATCH($L1,$D$2:$D$5000,0))

in F12

=IFERROR(INDEX($L$1:$L$250,SMALL(IF($K$1:$K$250=$G$7,ROW($L$1:$L$250)-ROW($L$1)+1,""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter

Copy down

**Kagesen** · 05-27-2016, 03:38 AM

Thanks for a very brilliant solution JohnTopley :-) it worked like a charm with a small modification to my sheet.

**JohnTopley** · 05-27-2016, 05:51 AM

Appreciate the rep and feedback.

List employee name based on multiple criteria - how?

LinkBack

Thread Tools

Rate This Thread

Display

List employee name based on multiple criteria - how?

Re: List employee name based on multiple criteria - how?

Re: List employee name based on multiple criteria - how?

Re: List employee name based on multiple criteria - how?

Re: List employee name based on multiple criteria - how?

Thread Information

Users Browsing this Thread

Similar Threads

YTD Sum based on multiple criteria from drop down list

How to get names from a list based on multiple criteria

Return list based on multiple criteria

[SOLVED] Calculating list data based on multiple criteria

List Results Based on Multiple Criteria

maintaining a list on a new tab based on multiple criteria

Pull from list based on multiple criteria

Bookmarks

Bookmarks

Posting Permissions