Using IFERROR with MATCH to get an Exact Match

Options

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?


Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    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.

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭
    Options

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!