Complicated Match/Lookup in between dates formula?
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Complicated Match/Lookup in between dates formula?

  1. #11
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,733
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by jwillits View Post
    Here is the actual data from my workbook. Note that this info is contained on different pages. In this case, could you write the formula trying to get the Related Weekly Spend number from Sheet2 into the column on Sheet1?

    The rules are the same as that I need the Tracking Number to Match from Sheet1 to Sheet2, and then I need the Date in Sheet1 to fall between the Date of the Matching Tracking Number. If there is none, I need it to go to the date range before it.
    To continue with the formula, it is important to answer the following:

    It is always the previous date.
    In an example.
    If the date is 25may, the ranges are
    a) 1apr - 20apr
    b) 26may - 31may
    So what is the corresponding range a) or b)?

    The nearest date is 26may, but do you want the previous date?

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

    To adapt the formula to rows and columns and sheets, you could upload your file.

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Regards Dante Amor

  2. #12
    New Member
    Join Date
    Jul 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by DanteAmor View Post
    To continue with the formula, it is important to answer the following:

    It is always the previous date.
    In an example.
    If the date is 25may, the ranges are
    a) 1apr - 20apr
    b) 26may - 31may
    So what is the corresponding range a) or b)?

    The nearest date is 26may, but do you want the previous date?

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

    To adapt the formula to rows and columns and sheets, you could upload your file.

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

    A would be correct, I need the nearest previous date, only if it is not between a set of dates.

  3. #13
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,733
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    To adapt the formula to rows and columns and sheets, you could upload your file.

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Regards Dante Amor

  4. #14
    New Member
    Join Date
    Jul 2018
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by DanteAmor View Post
    To adapt the formula to rows and columns and sheets, you could upload your file.

    You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.




    https://www.dropbox.com/home?preview=Sample+Data.xlsx

  5. #15
    New Member
    Join Date
    Apr 2019
    Location
    Porto - Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by DanteAmor View Post
    "*" is a nested if.

    if (condition, if (condition, true))
    or
    if (condition * condition, true)


    "," or ";" It is the argument separator that you have configured by region.
    So the "*" works as entering another IF in the 'True' section?

  6. #16
    New Member
    Join Date
    Apr 2019
    Location
    Porto - Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Couldn't download because asks for a login, although I think DanteAmor will find a solution for you.

  7. #17
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,733
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by tico_ocit View Post
    So the "*" works as entering another IF in the 'True' section?
    it is right.
    Boolean logic says:
    * = and
    + = or
    Regards Dante Amor

  8. #18
    New Member
    Join Date
    Apr 2019
    Location
    Porto - Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by DanteAmor View Post
    it is right.
    Boolean logic says:
    * = and
    + = or
    Nice! Didn't know! Always learning something! Thank you!

  9. #19
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,733
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Quote Originally Posted by tico_ocit View Post
    Nice! Didn't know! Always learning something! Thank you!
    In excel you always learn something new.
    Regards Dante Amor

  10. #20
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complicated Match/Lookup in between dates formula?

    Been using Excel and Access for 13 years, and i am still a beginner
    Sam_D_Ben

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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