Using IFERROR with MATCH to get an Exact Match

I have a lookup sheet where I am trying to pull data based on date. Some of the data in the sheet will be weekly and some daily. So my "Lookup sheet" has daily dates in the Date column.

What I'd like to do for the weekly data is say, if the date@row is not an EXACT match, return a 0 or a blank.

My similar formula works beautifully for the daily dates, but for the weekly dates, it's still pulling data on weeks that don't match.

For reference, I've attached a photo of my sheet. The white columns are daily numbers, this works well (please ignore the NO MATCH, I will correct that). The 2 columns in blue are weekly numbers and should only have numbers for the rows highlighted in yellow. All the other rows for the blue columns should be blank or have a "0".

Below is my formula for the blue columns - data is pulled from 2 separate sheets. I should say that I have also tried this with separate IFERROR statements (for both sheets) and the result was the same.

=IFERROR(INDEX({C-Completed Weekly Tasks}, MATCH(Date@row, {Date Weekly 1})) + INDEX({T-Weekly Completed Tasks}, MATCH(Date@row, {Weekly Date})), "0")

Here is the sheet. Is it possible to do what I want?

Find more posts tagged with

Accepted answers

All comments

Andrée Starå

Hi @Andrea Westrich

I hope you're well and safe!

Are all the ranges referencing the same two sheets?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some more 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!

Andrea Westrich

Hi @Andrée Starå,

Thank you for your reply. I'm 100% sure what you are asking for, but I'm going to try.

Here is the data from one of the "Weekly Sheets". This process is new, so I don't have much data.

This is a total of each week's incomplete and completed tasks. I am trying to pull this data into my lookup table (shown above). That lookup table also pulls totals of incomplete and completed tasks from daily task sheets, so you see daily dates, not weekly.

The totals from my "Weekly sheets" are correct, but why does it display the same totals for days not in my "weekly sheets"?

What I'm saying is I want data in my lookup table for weeks 8/9/21, 8/16/21 and 8/23/21, but for the days in between (8/10, 8/11, 8/12, 8/13, etc) the number should be Zero, because I don't have data from those dates. So if the date does not match exactly, return a 0 or a blank cell.

Does that help?

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.