Index Match - Multiple Criteria Referenced From Another Sheet

Vincent Carranza

I am building an employee lookup sheet. This sheet is referencing a sheet that is populated from an excel spreadsheet that HR runs. They will update this sheet monthly. The sheet has managers, area managers, district managers, AVPs, etc. So the Location IDs and titles appear more than once in the Location ID column and Title column. I am trying to figure out how to write an INDEX / MATCH formula to return an associate name if the Location ID is "X" and the position is "Y". I keep getting "#UNPARSEABLE". I have tried INDEX / MATCH as well as INDEX / COLLECT.

Formula 1

=INDEX(COLLECT({Area_Contact_List.DIS Range 4}, {Area_Contact_List.DIS Range 3}, [Primary Column]@row, [{Area_Contact_List.DIS Range 2}, [Primary Column]1]))

Formula 2

=INDEX({Area_Contact_List.DIS Range 4}, MATCH(1, ([Primary Column]@row = {Area_Contact_List.DIS Range 3} * [Primary Column]1 = {Area_Contact_List.DIS Range 2},0)))

Formula 2 explained

=INDEX({Associate Name column on another sheet}, MATCH(1, ([job title = {Job title column on another sheet} * Location ID on this sheet = {Location ID column on another sheet},0)))

Mark Cronk

Hi @Vincent Carranza ,

Try:

=JOIN(COLLECT({Area_Contact_List.DIS Range 4}, {Area_Contact_List.DIS Range 3},@cell= [Primary Column]@row, {Area_Contact_List.DIS Range 2}, @cell=[Primary Column]1),"")

To confirm your use of the [Primary Column]: You're using it for a name (@row) and a location (row1)?

Help?

Mark