Is VLookup the solution?

I'm assisting a team with tracking data related to volunteer hours and instances of volunteering. We have a list of 50-100 volunteer agencies, and I'm trying to find the best way to track this number. We have employees self-report their volunteer hours quarterly (but they can do it as frequently as they would like). Typically we have about 500 surveys each year.

Goal: Be able to see how many volunteer hours and instances of volunteer at each volunteer agency.

Idea: VLookup sounds like a good solution, but we may get additional volunteer agencies through the self-report survey. Would I manually have to add in each new agency? If there is a better solution than VLookup, please let me know!

Attached is the test version of the sheet. Thanks!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Julie Hale-Harris

What plan type are you on? If you're on a Business or Enterprise plan, what I would personally do here is use a Report to look at your sheet, perhaps filter by dates, then Group by the Agency and use the Summary feature to quickly create calculations (such as SUM all the hours per agency for that timeframe).

Here's a free webinar that goes through these features: Redesigned Reports with Grouping and Summary Functions

Let us know if that will work for you!

Cheers,

Genevieve

Julie Hale-Harris

Hi @Genevieve P.

Thanks for sharing this webinar! I tried to use the report feature. Do you know if there's a way to use the Group feature for multiple columns?

For the survey, employees can enter up to 4 agencies and hours served at each corresponding agency. When I tried to add another group, it show's a blank group because one row didn't have a 2nd agency listed.

Thanks!

Genevieve P.

Hi @Julie Hale-Harris

Thank you for this additional information! While you can group by multiple values, it will treat each column as a unique list and a sub-grouping, versus merging that content in with your original group.

You would need to have one column that lists all the agencies, and another one with the hours.

In this case normally I would suggest switching to use a "metric" sheet where you manually list out the 50-100 agencies, then use formula for your calculations (such as a cross-sheet SUMIFS).

You would do 4 SUMIFs, one per possible agency column and hours pair:

=SUMIFS({Hours 1 Column}, {Agency 1}, Agency@row) + SUMIFS({Hours 2 Column}, {Agency 2}, Agency@row) + SUMIFS({Hours 3 Column}, {Agency 3}, Agency@row) + SUMIFS({Hours 4 Column}, {Agency 4}, Agency@row)

However! If there will be an X number of new agencies listed that would need to be added, I still think a Report is the better way to go as a Report automatically captures new row data.

To use the Grouping feature in a report, we need one consistent Agency column (total) and one consistent Hours column. This is what I would do:

Create 4 copies of your current sheet set-up, with the numbers 1 - 4.
Add 2 new columns in each copied sheet with the same name, eg. "All Agencies" + "All Hours"
Set a formula in these two new columns to pull in a specific Agency and Hour pair from that row.

So:

Sheet 1 uses the formula =[Org/Event 1]@row in one column and =[Hours 1]@row in the other to pull in the first pair
Sheet 2 uses the formula =[Org/Event 2]@row and =[Hours 2]@row to pull in the second pair
And so on

In your original submission sheet, set up 4 separate COPY Row workflows that copy the same data into each of your 4 sheets.
Note: while this will copy every single piece of column data, you could hide all the columns except the specific Agency/Hours combination for each destination sheet
Create your Report to look at these 4 sheets (instead of the intake sheet)
Filter the report so you only get rows that are not blank in the "All Agencies" column
Use the helper "All Agencies" + "All Hours" to summarize across all submissions

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Julie Hale-Harris

@Genevieve P. Thank you for that feedback and sharing a workaround! I'm having trouble with the first step. I seem to have trouble with the @row function when referencing across sheets.

It gives me an "unparsable" message when I try to cross reference.

The formula currently reads as =SUMIF({2024 Employee Self-Report Range 1}, [2024 Employee Self-Report Range 5]@row, {2024 Employee Self-Report Range 3})

Here's the practice sheet I'm cross referencing!

Thanks

Genevieve P.

Hey @Julie Hale-Harris

The @row reference says to look at the cell in this current row, meaning in the current sheet. This means the column name [in these] needs to be a column name in this sheet.

In your case, you want to search for the value in the Primary Column, yes?

Try:

=SUMIF({2024 Employee Self-Report Range 1}, [Primary Column]@row, {2024 Employee Self-Report Range 3})

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.