Vlookup like a SumIF

Hello,

When we use a sumif formula, we can specify to check for a specific criteria in a specified colunm, and then, send back the data of another specific column (on the row where data was found).

It's great with numbers.

I would like to do the same, but with text. As the sumif doesn't work with text, I tried using Vlookup formula. My problem is that it asks for how many columns to count for sending back data. Please refer to the "7" in the following formula. I would like to be able to specify a column name where to get the answer from, not a column count. I reference another sheet with my formula, and if, for any reason, columns order change in the reference, the formula becomes wrong.

=VLOOKUP([Task Name]3; {BP - Grille de projets Plage 3}; 7; false)

Thank you,

Find more posts tagged with

Accepted answers

All comments

Tvl_97

I don't think you can use the column names for vlookup, but you can "cheat" by adding a new row at the top of your dataset and rename the row values the same as your column names. With that, you can use the match function to find your specified column:

=VLOOKUP([Task Name]3, {BP - Grille de projets Plage 3}, MATCH("Name of your column","The entirety of your row 1", false),0)

This problem with this method is that you won't be able to do Convert to Column Formula for any of the columns

Marie PH

Thanks, unfortunately, I need to keep column formulas, but nice "out of the box" thinking. :)

Andrée Starå

Hi @Marie PH

I hope you're well and safe!

To add to Tvl_97's excellent advice/answer.

Here's a possible workaround or workarounds

You could use the Sheet Summary section instead, converting it to a Column Formula.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

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.