Index / Match / Join Formula across 2 sheets
Hi,
I am trying to create a formula to identify a contact name from one sheet (named "LS Store Dev Contacts List"), based on data from two columns ("Business Division" and "Project Type") from a second sheet (named "Test Fit Tracker"). So for example, if on the "Test Fit Tracker", "Regional" (Biz Division) and "New" (Project Type) are identified on a row, the formula will input the correct Contact person associated with that combination from the LS Store Dev Contacts List.
I am currently getting #Unparsable with this formula:
=INDEX({LS Store Dev Contacts List | Business Division:{LS Store Dev Contacts {LS Store Dev Contacts List | Project Types}, MATCH(JOIN([Business Division]@row:[Project Type]@row, " , "), {LS Store Dev Contacts List | Biz Div and Proj Type:{LS Store Dev Contacts List | Biz Div and Proj Type},1){LS Store Dev Contacts List | Contact Name})}))
Here is the Contact List:
Any help would be greatly appreciated.
Thanks, Mark
Answers
-
If I understand your question correctly, I'd think you would want to try using a vlookup() instead. You want to return the value in Contact Name, based on the data in Business Division. Then you want to put that into an If statement, and do another VLOOKUP and pull the Contact Name if it matches on Project Types.
https://app.smartsheet.com/sheets/5v9rRQ3hq9WPqqgP48ggxQrmR74jvR5R6rVFGWm1?view=grid
-
Problem with that is some business units have multiple different people, and that is why it must cross reference both the biz dev and project type as the same time.
-
So there are 2 ways I know to get this result. The first way is similar to what you seem to have tried.
Index({Return},match(Join([L 1]@row:[L 2]@row),{search},0)
Where you use a helper column in the search sheet to join multiple columns together then search by the joined values.
The second way, and way that I am more prone to use to search, is index(collect
=index(collect({return range},{first criteria},Criteria1@row,{second criteria},Criteria2@row),1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!