Matching Company Name Based on Member Name Chosen

Jonna Critchley

Hi There!

We have sheets called Meeting Trackers that we log the name, company name, and company category of the attendees of each workgroup meeting. We have 30+ meeting trackers that we've maintain between 5 meeting facilitators.

Through Power BI we collect all the Meeting Tracker information and it should give us what meetings a person or company has attended, how many times, etc.

One of the biggest issues is the spelling...the member's name, the company name and making sure the company category is consistent.

Our organization cannot afford data mesh or data shuttle at this time, so I'm looking for some kind of work around.

Ideally, we'd want the meeting facilitator to be able to begin typing a name in and in the drop down it would begin pulling up the correct name, that would then enter the matching company name in the next column and the company's category in the column after that.

Is there a combination between a helper sheet and formulas that would get me to this?

Paul Newcome

You would use an INDEX/MATCH

=INDEX({Reference Table Column To Pull}, MATCH(Name@row, {Reference Table Name Column}, 0))

Paul Newcome

You would need to set up a reference sheet that has everyone's name listed in one column and the appropriate category in another.

Then you can use a VLOOKUP or the preferred INDEX/MATCH with cross sheet references to pull the category based on the name entered.

Jonna Critchley

@Paul Newcome if my column names are Name, Company Name and Category - what would the formula be?

Is there an example formula you can give me to get me started?

Paul Newcome

You would use an INDEX/MATCH

=INDEX({Reference Table Column To Pull}, MATCH(Name@row, {Reference Table Name Column}, 0))

Jonna Critchley

@Paul Newcome this is working perfectly, thank you so much!

Paul Newcome

Happy to help. 👍️

Jonna Critchley

@Paul Newcome is there a formula for me to populate the dropdown list of names?

The biggest issue I see now with this process is if there is a new name, one not already present in the dropdown, I would have to change the dropdown for 30+ trackers. Would there be a universal way from the main sheet I'm pulling and matching the company name and category to add a new name without doing it for each dropdown?

Paul Newcome

If the name is not present in the dropdown already, then wouldn't it not be present in the master sheet as well?

Jonna Critchley

@Paul Newcome that is correct.

Currently I have it setup this way:

30+ meeting trackers with the first column = Name (dropdown that I've manually populated with the list of names from the "master"), second column = Company Name (this is pulled into each tracker based on the Name dropdown selection and from the formula you provided to the "master"), third column = Category (this is pulled into each tracker based on the Name dropdown selection from the formula you provided to the "master")

If a new person attends a meeting, they would need to be added to the "master", but then they would need to be added to the 30+ meeting tracker's name dropdown list so that it can be selected.

I think you're thinking they would just type in the name, but the issue with letting them "free form" type the name in is if they misspell the name or use a variant of the name, for instance saying Bill Smith instead of William Smith which is what he would be recognized as in the "master" list.

Paul Newcome

This is a perfect use case for the premium add-on Data Shuttle. Data Shuttle will allow you to update a master list and push that list out to dropdown columns (every 15 minutes at the most frequent).

You can also use the API for this.

Otherwise you are unfortunately looking at a manual process.

Jonna Critchley

@Paul Newcome aw that's what I was afraid you were going to say...ok, thank you sir!

Sincerely appreciate your quick and accurate assistance, it's been most helpful!