IF(INDEX(MATCH formula

Eric D

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

 

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))