Index / Match / Join Formula across 2 sheets

Mark Parsons

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

thoth

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

Mark Parsons

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.

L_123

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)