Solved

can a VLookup include "Exclude roll forward if weekend dates returned"

Posted on 2014-09-25
10
204 Views
Last Modified: 2014-09-25
Can an expert resolve this for me.

I am using a VLookup i.e.:

=IF(ISERROR(VLOOKUP(A2,Nov!D:I,6,0)),"",(VLOOKUP(A2,Nov!D:I,6,0)))

But if the date it returns is a weekend I need it to to be rolled forward to the next working day i.e. Monday

so if the date it returns is 27/9/14 I need it to change to 29/9/14

if the date it returns is 28/9/14 I need it to change to 29/9/14

Is this possible

Thanks
0
Comment
Question by:Jagwarman
  • 6
  • 4
10 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
Assuming you have Excel 2007 or greater, you can use IFERROR(A1,""), instead of IF(ISERROR(A1),"",A1) - it's quicker.

Here's the answer:

=IFERROR(VLOOKUP(A2,Nov!D:I,6,0)+CHOOSE(WEEKDAY(VLOOKUP(A2,Nov!D:I,6,0),1),1,0,0,0,0,0,2),"")

Open in new window

0
 

Author Comment

by:Jagwarman
Comment Utility
wow that was a fast reply. Brilliant I would never have worked that out thanks Phillip
0
 

Author Closing Comment

by:Jagwarman
Comment Utility
Very helpfull Expert.
0
 

Author Comment

by:Jagwarman
Comment Utility
Phillip,

sorry to trouble you again on this one but, when the VLookup does it's work, if the field it looks up is blank it is returning 02/01/1900

I thought that IFERROR would stop that?

Thanks & Regards
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
No, "IFERROR" or "IF(ISERROR" will only return a blank if there is an error.

If column I is nullable, then VLOOKUP will return a zero (instead of a blank value), and you need to check for it:

=IFERROR(if(VLOOKUP(A2,Nov!D:I,6,0)=0,"",VLOOKUP(A2,Nov!D:I,6,0)+CHOOSE(WEEKDAY(VLOOKUP(A2,Nov!D:I,6,0),1),1,0,0,0,0,0,2)),"")

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Jagwarman
Comment Utility
sorry I have another problem now

The date it is looking up is 17/11/2014 which is a Monday but it is returning 19/11/2014

Would you know why?
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
It isn't for me. Are you sure that there isn't an earlier version of the thing that it is looking up, which has an earlier date?
0
 

Author Comment

by:Jagwarman
Comment Utility
this is going to be one of those bizzare things. I have played around with the dates but can't resolve
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Do you want to post your spreadsheet, or is it confidential?
0
 

Author Comment

by:Jagwarman
Comment Utility
it was me, sorry and BIG thanks for your help

instead of D:I,6,0   D:I,6,0   D:I,6,0

I somehow put

D:I,6,0   D:I,6,0   D:I,7,0
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now