IF date value returned from Index/Match is blank, return prior cell that returns a value

**gunnerau** · 09-11-2014, 07:31 PM

Hi all,

I have set up the attached spreadsheet to track sales in relation to selling days. An Index/Match formula is setup to return a value of how many selling days have progressed based on looking up the actual day of the month minus one day, and matching the month .

My problem is, when it looks up a day that is not a sales day (blank cell) by referencing L2, it doesnt return a value. I need a formula which says if a blank value is returned when matching the day in L2, return the value of the most recent selling day. This would be the prior cell with a value in it.

Eg. September 14 returns a blank, I need it to return the value for September 12 (the most recent selling day)

All help appreciated

Cheers

**JBeaucaire** · 09-11-2014, 08:00 PM

Something like this perhaps in P7:

=MAX(OFFSET($J$13, 1, MATCH($K$2, $K$13:$U$13, 0), $L$2, ))

Change your formula in S7:

=IFERROR(K7/P7,"")

**Haseeb Avarakkan** · 09-11-2014, 08:07 PM

Try this,

=MAX(INDEX(K$14:U$44,1,MATCH(TEXT(K$5,"mmmm"),K$13:M$13,0)):INDEX(K$14:U$44,DAY(K5),MATCH(TEXT(K$5,"mmmm"),K$13:M$13,0)))

**FDibbins** · 09-11-2014, 08:29 PM

Instead of that big table and the index/match needed to pull the days worked, try this in P7 instead...
=NETWORKDAYS(EOMONTH(TODAY(),-1),TODAY()-1)

You can test it by changing the last -1 to +1 (10 days), then to +2 (10 days), +3 (10 days), +4 (11 days)
You would only need to use =NETWORKDAYS(EOMONTH(TODAY(),-1),TODAY()-1) though, just to make sure you understand

**gunnerau** · 09-11-2014, 08:40 PM

Thanks all, I will give this a go

IF date value returned from Index/Match is blank, return prior cell that returns a value

LinkBack

Thread Tools

Rate This Thread

Display

IF date value returned from Index/Match is blank, return prior cell that returns a value

Re: IF date value returned from Index/Match is blank, return prior cell that returns a val

Re: IF date value returned from Index/Match is blank, return prior cell that returns a val

Re: IF date value returned from Index/Match is blank, return prior cell that returns a val

Re: IF date value returned from Index/Match is blank, return prior cell that returns a val

Thread Information

Users Browsing this Thread

Similar Threads

Index/ Match Formula, How to return blank cell as a blank not 0

[SOLVED] I need to Return Multiple Matches While Index(Match) only returns the First one.

Calculate month to date returns, index and match

Index/Match to return a blank cell

Vlookup or Index / Match for multiple criteria and return a date value from cell

Bookmarks

Bookmarks

Posting Permissions