IF(INDEX(MATCH formula

Hey folks,

Thought I'd reach out to see if anyone could assist with my IF(INDEX formula. The columns involved with this one are Today's Date, Date2(abbreviated name for the day), daily sales qty's. I have a Dashboard where I return/display the Qty for the previous day's sales.

My issue is, the formula in it's current state is not excluding Sat and Sun sales Qtys which are typically 0. I'd like to reference Friday's sales when match is Sun, Mon. Here's my current formula which returns 0's for Sat/Sun sales. :=INDEX(Sales:Sales, MATCH(TODAY() - 1, [Today's Date]:[Today's Date], 1))

My first though was to try to use WORKDAY which seemed inclined to exclude Sat, Sun, but I couldn't find any good examples that utilize WORKDAY in with INDEX function. Then got the idea to use the hard coding as references in second Date column(Date2)

Here's my first crack at this below which is returning #UNPARSEABLE:

=IF(INDEX([Sales]:[Sales], MATCH(TODAY()-2,[Today's Date]:[Today's Date],(Date2@row = "Sun", IF(INDEX([Sales]:[Sales], MATCH(TODAY()-3,[Today's Date]:[Today's Date],(Date2@row = "Mon", IF(INDEX([Sales]:[Sales], MATCH(TODAY()-1,[Today's Date]:[Today's Date],(Date2@row = "Tue", "Wed", "Thu", "Fri", "Sat"),1)))

Screenshots below. If anyone has any pointers, I'd greatly appreciate it. Thanks

Find more posts tagged with

Formulas

Comments

Paul Newcome

Try using an IF statement within your today function to determine -1, -2, or -3.

TODAY(IF(WEEKDAY(TODAY() = 2, -3, -1))

This means that if today is Monday, the today function will look like this:

TODAY(-3)

which will pull Friday's data.

If it is any other day of the week, it will show as TODAY(-1) which would be the previous day's data. This would pull Friday's data on Saturday and Saturday's data on Sunday, but I am assuming that since you are only concerned with showing weekday data, you are not viewing the dashboard on the weekends.

If you need it to accurately pull Friday's data on Saturday, Sunday, and Monday, that is very easy to accomplish as well. Just let me know and I'll put that out here for you.

Eric D

Hello Paul,

Thanks for your suggestion using WEEKDAY in the Today function! I've placed this in the formula but I'm getting #UNPARSEABLE still. I'm having a time getting the order and syntax correct I think. Here's the formula I've got so far:

=IF(INDEX(Sales:Sales, MATCH(TODAY(IF(WEEKDAY(TODAY() = 2, -3, -1)), [Today's Date]:[Today's Date], 1)))))

Seems like when I get to MATCH, the formula tooltip is looking for the INDEX row_index. See screenshot:

Will continue tweaking it. Really appreciate your insight.

Paul Newcome

That is correct. The INDEX function is looking for a number to determine which row to pull from within the specified range.

MATCH will return a number based on where specific criteria is found in a cell within a range.

So you use the MATCH function to pull a number from a range based on criteria and then use that number to specify a row number by nesting it within your INDEX function.

Your unparseable error is coming from your first TODAY function. You aren't closing that out. I also suggest using a 0 for the third portion of your MATCH statement. I personally get the most accurate results from that. The other issue is that you are not finishing out your initial IF statement.

=IF(INDEX(Sales:Sales, MATCH(TODAY(IF(WEEKDAY(TODAY() = 2, -3, -1))), [Today's Date]:[Today's Date], 1)) = something, then do this, else this)

In all reality, based on the details you've provided that initial IF is not needed at all. You should be able to use...

=INDEX(Sales:Sales, MATCH(TODAY(IF(WEEKDAY(TODAY() = 2, -3, -1))), [Today's Date]:[Today's Date], 1))

Sign in to join the conversation!

Quick Links

Trending discussions

INCORRECT ARGUMENT SET on an =IF function
I'm trying to pull in the contact information of a second HR Business Partner, contingent on the department of the individual, from a separate reference sheet. However, not all departments have a second HRBP— so if the column of "second HRBP contact info" is blank, I don't want anything to be pulled into the new sheet's…
Automation not triggering when % Complete shows '100% instead of 100%
I have an automation set up so that when the Status column changes to Complete, Canceled, or Not Needed, Smartsheet automatically updates the % Complete column to 100% (Text/Number column type). After that, another workflow should trigger to alert the next person that their task is ready. This worked fine before, but…
Index Match - # NO MATCH error
Hello, My index match formula cross sheet is giving error since yesterday. =INDEX({VER DT4DT Tools Range 1}, MATCH([App CI]@row , {VER DT4DT Tools Range 2}, 0)) I have checked the format of columns in both sheet and also retyped formula. any advice?