Multiple MATCH with WORKDAY

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here is my data:

Rich (BB code):
Col A          Col B                   Col D           Col E
01/01/2010a31/12/2009 a
02/01/2010b01/01/2010 b
03/01/2010c02/01/2010 c
04/01/2010 d03/01/2010 d
<tbody> </tbody>

In cell G1, I type:

Rich (BB 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:

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





 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)<workday(index(c1:c4,match(b1,d1:d4,0)),3)
</workday(index(c1:c4,match(b1,d1:d4,0)),3)
 
Last edited:
Upvote 0
Thanks but I've sorted it out now.

Seems you have to add 0 to the arguments.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top