Automatic related rows based on a specific value from one or multiple workbooks/sheets

❓ Questions & help
1

So, new user as of today, but couldnt figure out if this is currently possible.

I’m first off trying to set up an applicant tracking system. Where I work we recruit for new team members every 6 months. We will occasionally have people apply multiple times, or they could apply in January and then again in July.

I’d like to be able to relate an applicant row, either through something like mobile phone number or email address and have it automatically populate that relationship from either the current sheet, or from different sheets so I can find previous details about why they were rejected or did not attend interviews or our training for the position.

MATCH() function will return a position in a list, but only the first instance of that match, but then I’m not sure how to use that to create a related row, or find multiple rows.

I might be looking for too much, but it doesn’t hurt to ask if anyone knows how to do this already, or if its something worth adding to the system.

2

Hi Daniel,

A warm welcome to the community.

Regarding the requirement you have shared, few pointers:

  • Related lookups over relationships in SSDC are out-of-box alternatives for using lookup functions like MATCH/XLOOKUP/VLOOKUP.

  • Given the use-case you can choose to use either of them.

  • For very advanced requirements, you may also mix both approaches.

Have shared a sample prototype with you in this regard, request you to review the same.

Few limitations as of today:

  • We do not support aggregate functions like COUNT, MIN, MAX over multiple related lookup matches.
  • Conditions on multiple columns/fields are not supported ex. array formulas, when using lookup functions like MATCH.

However, these are items in our product backlog which we plan to implement going forward.

1 Like
3

Fantastic, thank you for having a look at this. Its almost midnight here so I will have to look at the shared spreadsheet tomorrow, but the quick look I had just now is close to what I had thought about. I’m a spreadsheet novice in general, but am slowly learning various tools.