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
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
That worked! Thank you!
-
Happy to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!