Candidate and Company Matching System

Hi @elicos

I don't believe there is a way you can do this by having one option per row.

One of the complications is the multi-select fields. There isn't a function that can use one multi-select cell and individually match each element in another sheet's multi-select column - it would look for an exact match, meaning the combinations selected ("Remote, Canada" instead of "Remote" and then "Canada"). However if we broke out one sheet's multi-select field into single selections, then we can search for this single selection among another multi-select column.

What I would do in this instance is set up a sheet that has each Job listed broken out by its Role and Country combinations.

Ex:

Row 1 - Company 1 / Operations / Canada

Row 2 - Company 2 / Operations / Canada

Row 3 - Company 2 / Operations / Remote

Row 4 - Company 2 / Sales / Canada

Row 5 - Company 2 / Sales / Remote

You'll need to set up each row with separate options, not multi-select. This is so that you can use a JOIN(COLLECT formula to bring in any/every Candidate that matches each option. We wouldn't be able to parse them down multiple rows, but you could collect all the Candidates that match each option:

=JOIN(COLLECT({Email Column}, {Location Column}, HAS(@cell, Country@row), {Job Category}, HAS(@cell, [Department Roles]@row)), ", ")

Ex:

Row 1 - Company 1 / Operations / Canada / Test 1, Test 2, Test 4

Row 2 - Company 2 / Operations / Canada / Test 1, Test 2, Test 4

Row 3 - Company 2 / Operations / Remote / Test 1, Test 4

Row 4 - Company 2 / Sales / Canada / Test 3

Row 5 - Company 2 / Sales / Remote / Test 3

You could then have a Report that Groups by Company so you can see all the possible candidates, however there will be duplicates (like in Row 4 and 5) above.

I hope this helps,

Genevieve