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.

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

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!

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

@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å

@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!

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.