Index Match - Multiple Criteria Referenced From Another Sheet

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)))

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/17/21 Answer ✓

    Hi @Vincent Carranza ,


    =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)?



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!