Match Volunteers to Meetings

I need a way to match a list of volunteers to meetings in their area.

Sheet A houses volunteer information, and the input is a Smartsheet form. Very basic, houses employee info and state (has multiple name columns because I added the "Name" column as a contact column while I was playing with formulas. Ultimately, it will have one name column)

Sheet A

Sheet B houses all the meeting information by state.

Sheet B

Each state has many meetings, so we are currently at approximately 1600 rows on sheet B. I would like a way to match the volunteer list to the meeting list. I am not attached to a way of doing this; for instance a dashboard or a report. I just need an easy way to match the volunteers so our volunteer coordinator can easily identify the pool. The trouble I've run into is that we have so many meetings, so my solutions aren't feasible.

I've tried INDEX(MATCH to list all the volunteers with a matching state in one cell (for instance, on sheet A, anyone with AL in the "Your State' column would show up on the AL line in sheet B. This would give me an easy way to see the pool of Alabama volunteers to pull from.

Any ideas??

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Christi,

    Why doesn’t INDEX/MATCH work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi Andree,

    It's probably 100% user error, I'm pretty new to formulas in general.

    Here's what I used:

    =INDEX({Your State},MATCH(Meeting [email protected],{Name},0))

    It's my understanding that I'm telling the system to return any name where the "Your State" column matches the state listed in the Meeting Name column (I put the formula into a cell next to the two character state name, such the line that says AZ). However, the formula came back as #unparseable

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Ok.

    That will only show the "first" match. If you want to show all you'd need to use it with COLLECT.

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I would really appreciate it. I shared my sheets, thank you

Help Article Resources