Is there a limit to adding VLOOKUPs?

I am working to sum multiple columns that match a specific criteria. I was able to accomplish this with the SUM formula and using VLOOKUP. It works when I add 2 VLOOKUP formulas together but if I add more, it says #NO MATCH. Is there a limit?


Example:

=SUM(VLOOKUP(Description@row, {Unit 3}, 2, false) + VLOOKUP(Description@row, {Item 4}, 2, false))

**This formula works to add the items together.


=SUM(VLOOKUP(Description@row, {Item 1}, 2, false) + VLOOKUP(Description@row, {Item 2}, 2, false) + VLOOKUP(Description@row, {Item 3}, 2, false))

**This one does not. They are all following the same criteria needed for a VLOOKUP.


Help!


Would also take suggestions for how else to SUM numbers from several columns that meet a specific criteria.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/27/21

    Hi @Kelly Pratt

    I hope you're well and safe!

    No, there aren't really any limits to adding VLOOKUPs except the character count (4000) or the cross-sheet formula limits, but it doesn't seem to be the issue.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

  • Kelly Pratt
    Kelly Pratt ✭✭✭✭✭

    @Andrée Starå - Thank you for your reply! Here is a little more information. I am using one sheet as a form for users to submit a quantity of specific items they used within a project that day. They can submit the items in any order (as they can select from the drop down) and they can include quantities for the same item for several days (although they would show as separate rows). I am trying to create a weekly summary sheet to add up the quantities submitted.

    I am attempted to using SUM and VLOOKUP formula to do so, but I have encountered a few issues.

    1. For certain rows, once I add a third VLOOKUP formula, it no longer works.

    2. It is only looking for the first match in the column to add in the formula, but there could be several submissions in the same column for the same item and we'd want to add them together.

    I'm not able to share the actual sheets I'm working on, but I've copied them and am using cookies as an example as the item being submitted doesn't really matter. I will share those with you so you can see!

    Here's what the formula looks like:

    =SUM(VLOOKUP(Description@row, {Unit 1}, 2, false) + VLOOKUP(Description@row, {Cookie Quantities Range 1}, 2, false) + VLOOKUP(Description@row, {3}, 2, false))

  • Kelly Pratt
    Kelly Pratt ✭✭✭✭✭

    @Andrée Starå - I was actually able to figure this out using a SUM (SUMIF formula. Thank you for taking the time to answer and review for me!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 07/28/21

    @Kelly Pratt

    Excellent!

    Happy to help!

    Glad you got it working!

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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!