tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
An extension to this problem:

Rich (BB code):
https://www.mrexcel.com/forum/excel-questions/1107696-approximate-match-vlookup.html



I want to amend this formula:

Rich (BB code):
=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),"Y","N")



to this:

Rich (BB code):
=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0))+3,"Y","N")



and it's fine.

But actually I want to use the WORKDAY function:

Rich (BB code):
=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0))+WORKDAY($I$3:$I$7,3),"Y","N")



but it's not returning the correct results.

Can someone tell me what's wrong?

Thanks


<strike>
</strike>
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
What results are you getting ?
What do you think the results should be, and why ?

According to Excel's built in help function, the WORKDAY function requires its first argument to be a reference to a single date, the start date.
You seem to be giving it a range.
What results do you get for this on its own . . .
WORKDAY($I$3:$I$7,3)

?
 
Upvote 0
Table1Table2
Field1Field2DateField1Field2Date
1a05/01/20101a04/01/2010
2b06/01/20103c03/01/2010
3c07/01/20104d04/01/2010
4d08/01/20105e03/01/2010
5e09/01/20102b06/01/2010

<tbody>
</tbody>

What I am trying to do is if Field1 matches AND Field2 matches AND the date in Table1 is later than the date in Table2 AND if the date in Table1 is earlier than the (date in Table2 +3 workdays).
 
Last edited:
Upvote 0
I get #VALUE !, whether I enter it "normally" or as an array formula.

This helped:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.excelforum.com/excel-formulas-and-functions/922894-workday-function-and-array-formulas.html


[/FONT]
 
Last edited:
Upvote 0
Yes, I think you're using the workday function incorrectly.

It looks like you're trying to add 3 working days to some start date, yes ?

I think you need to specify what the start date actually is, and use that in the workday function.

For example
+workday(a1,3)
where the start date is specified in A1.

I know that you are not actually specifying the start date in A1.
So, question, how are you actually identifying the start date ?
 
Upvote 0
Afraid it doesn't return the correct result.

I'm going to add additiional columns and build it, instead of having one single (but massive) formula.
 
Upvote 0
How about this ?

=IF(C3>WORKDAY(INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),3),"Y","N")
 
Upvote 0
Almost.

What I need is this:

Rich (BB code):
=IF(C3<=WORKDAY(INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),3),"Y","N")

Thanks for your help.



 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
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