Results 1 to 3 of 3

Thread: Multiple MATCH with WORKDAY
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple MATCH with WORKDAY

    Here is my data:

    Code:
    Col A          Col B                   Col D           Col E
    
    01/01/2010 a 31/12/2009 a
    02/01/2010 b 01/01/2010 b
    03/01/2010 c 02/01/2010 c
    04/01/2010 d 03/01/2010 d
    In cell G1, I type:

    Code:
    =MATCH(1,(B1=E1:E4)*(A1<=WORKDAY(D1:D4,3)),0)
    
    


    and enter it as an ARRAY formula.

    I expected the result to be 1 but instead it returned #N/A.

    What is wrong?

    What I am trying to match is the condition a in Column E and if the date is less than or equal to 3 workdays after the date in Column D.

    Thanks


    EDIT SORTED:

    Should be:

    Code:
    =MATCH(1,(B1=E1:E4)*(A1<=WORKDAY(D1:D4+0,3+0)),0)
    
    




    Last edited by tiredofit; Aug 27th, 2019 at 07:06 AM.

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple MATCH with WORKDAY

    Well for one thing, are you getting confused with your column references ?
    When you posted your data, it appeared that ALL data was in columns A:D, but your formula (and later comments) refer to column E.
    It would help us if we were clear on this point.

    But apart from that, what are you actually trying to achieve ?

    Are you trying to calculate whether the date in col A for item "a" in col B is less than 3 working days after the date in column C that applies to item "a" in column D ?

    If YES, then this formula will indicated TRUE if those conditions are met, and FALSE if not (and #N/A if the item in col B is not found in col D)
    Code:
    =A1< WORKDAY(INDEX(C1:C4,MATCH(B1,D1:D4,0)),3)
    
    Last edited by Gerald Higgins; Aug 27th, 2019 at 07:10 AM. Reason: Edit to add possible formula solution
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple MATCH with WORKDAY

    Thanks but I've sorted it out now.

    Seems you have to add 0 to the arguments.

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
  •