What formula to write - Vlookup or Index/Collect Formula???

ksabrin

Hello,

I am creating a New Hire and CFO Buddy Tracker to assist me with paring new hires to mentors within the company.

I have two sheets set up one that has the name of all the mentors currently available and another with the list of New Hires.

The idea is that on the Mentor list I want it populate with the name of the New Hire they are assigned to on the New Hire List.

I have attached my sheet set-up below.

I have tried Vlookups and Index/Collect Functions but I get error messages.

MichaelTCA

@ksabrin

Use {New Hires} as the range to pull from. Not the buddys range.

MichaelTCA

Hello @ksabrin

If you have 1 buddy with 1 mentor, then Index/Match would be a good function to use.

If you have multiple criteria or multiple buddies with a single mentor, then Index/Collect would be better.

Depending on the type of data you are using, you can use other functions with Collect, such as Max or Min when analyzing a group of data. Such as who is first in line to be trained by mentor #1 and as training is completed, a second criteria will acknowledge it and automatically update the value to the next person. You can get fancy with it.

What do the functions you use currently look like?

ksabrin

@MichaelTCA

So at most 1 buddy can be paired with two new hires.

The functions I have used is an Index/Collect with the starting being Index(Collect([Buddy Name]:[Buddy Name], {Reference to Buddy Program Tracker "Buddy Name" Column", = BuddyName@row....) and then I am pretty lost after that.

MichaelTCA

@ksabrin

If that's all the criteria you need, then INDEX({Buddy Tracker Program Range},MATCH(BuddyName@row,[Buddy Name]:[Buddy Name],0)) would work, but it will only find the first result out of the range.

If the function is used on the sheet with the new hires and you're assigning a buddy to each, INDEX/MATCH would be my suggestion.

Otherwise,

Multiple results in a single cell, you could try this and use wrap text on the column:

JOIN(COLLECT({Reference to Buddy Program Tracker}, [BuddyName]:[BuddyName], [BuddyName]@row), CHAR(10))

Automations may not work properly if you use multiple lines in a single cell. Depends on what exactly you're doing with it.

ksabrin

@MichaelTCA

That formula is just returning the buddy name again and not the new hire name

MichaelTCA

@ksabrin

Use {New Hires} as the range to pull from. Not the buddys range.