Matching Company Name Based on Member Name Chosen

Jonna Critchley
edited 09/12/23 in Formulas and Functions

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?

Best Answer


  • Paul Newcome
    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.

  • @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would use an INDEX/MATCH

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

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • @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
    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?

  • @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
    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.

  • @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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!