+ Reply to Thread
Results 1 to 9 of 9

How to avoid Particular Date with Vlookup ?

  1. #1
    Registered User
    Join Date
    10-21-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    44

    How to avoid Particular Date with Vlookup ?

    hi to all experts,

    I am using the Vlookup formula to get the accurate date on the basis of Report No.

    but here I want to avoid Fridays on Cell A2, for Example :

    The Report No. 41 is showing Friday, in this regard I want that if I put Report No. 41 so it must jump to Saturday Automatically.

    Thanks.....
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to avoid Particular Date with Vlookup ?

    Hi,

    maybe:

    =vlookup($A$6,$E$3:$F$269,2)+(weekday(vlookup($A$6,$E$3:$F$269,2))=6)

    Hope it helps
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    10-21-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: How to avoid Particular Date with Vlookup ?

    thanks bro.....

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to avoid Particular Date with Vlookup ?

    Hi,

    thank for your kind feedback

    تحية من فلورنسا

  5. #5
    Registered User
    Join Date
    06-12-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to avoid Particular Date with Vlookup ?

    This is working fine. Could you explain the logic in steps

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to avoid Particular Date with Vlookup ?

    Hi,

    the new segment (weekday(vlookup($A$6,$E$3:$F$269,2))=6) checks if

    vlookup($A$6,$E$3:$F$269,2)

    is a Friday.

    I've used WEEKDAY. Weekday function returns a day of a week. 1 is Sunday ... 7 is Saturday.

    ----------------------------------
    Example, today is Sunday

    =weekday(today()) is 1

    ----------------------------------

    In our formula, if (weekday(vlookup($A$6,$E$3:$F$269,2))=6), if date returned by vlookup is Friday, formula returns TRUE ( 1 )

    The segment adds 1 to the date returned by

    =VLOOKUP($A$6,$E$3:$F$269,2)+1

    for days different than Friday


    (weekday(vlookup($A$6,$E$3:$F$269,2))=6)

    returns FALSE and adds 0 (days) to vlookup date.

    =VLOOKUP($A$6,$E$3:$F$269,2)+0



    Regards
    Last edited by canapone; 11-10-2013 at 03:55 AM.

  7. #7
    Registered User
    Join Date
    06-12-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to avoid Particular Date with Vlookup ?

    Thanks a ton to explain in details.

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to avoid Particular Date with Vlookup ?

    Hi,

    you're welcome.

    Thanks for your nice feedback.



    नमस्ते

  9. #9
    Registered User
    Join Date
    06-12-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to avoid Particular Date with Vlookup ?

    Victor, kindly mark this as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to add date to cell, and if already present avoid inserting new date over the existing
    By Mel56 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2013, 01:11 PM
  2. INDIRECT function within VLOOKUP to avoid #REF error???
    By Hozcat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-03-2012, 10:30 AM
  3. Replies: 5
    Last Post: 05-02-2011, 05:44 AM
  4. How to avoid the occurance of unexpected VLOOKUP #N/A errors
    By luannc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-16-2011, 02:16 PM
  5. UDF to avoid huge VLOOKUP erea
    By yoniman in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-11-2011, 10:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1