Vlookup like a SumIF



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,


  • Tvl_97
    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å
    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!


    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!


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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!