Nested IF or MATCH or JOIN+MATCH ?
Dear Community,
I am trying to retrieve the information of one cell depending on several criteria:
- Match week number from a date with Week Column to retrieve the payment amount
- Prioritize between 2 dates (though between 2 week numbers) which one will be retrieved (Actual Payment Week will have priority over Expected Payment Week), However these 2 weeks are often not the same, so I need to create IF conditions.
Here is my Formula which returns #INCORRECT ARGUMENT SET:
=IF($[Actual Payment Week]@row = [2019 Wk 01]$2, $[Exact Amount Received]@row, IF(AND(NOT(ISBLANK($[Actual Payment Week]@row)), ($[Expected Payment Week]@row = [2019 Wk 01]$2), "", IF($[Expected Payment Week]@row = [2019 Wk 01]$2), $[Invoice Value]@row, "")))
Here is the boolean logic I am willing to express in my formula:
__________________________________________________
IF :
$[Actual Payment Week]@row = (Wk01)$2
IF TRUE RETURN : $[Exact Amount Received]@row
IF FALSE RETURN : IF :
$[Actual Payment Received]@row IS NOT BLANK
AND
$[Expected Payment Week]@row = [2019 Wk01]$2
IF TRUE RETURN : BLANK (“”)
IF FALSE RETURN : IF :
$[Expected Payment Week]@row = [2019 Wk01]$2
IF TRUE RETURN : $[Invoice Value]@row
IF FALSE RETURN : BLANK (“”)
__________________________________________________
I have been turning around for quite a while but I can't see the mistake..
If you have any suggestions or directions to recommend, I would be glad to have your feedback.
Thx a lot !
Michaël
Comments
-
It looks like you have an extra set of parenthesis.
The extra open parenthesis is going to be at the start of your second logical statement within the AND function, and the extra closing parenthesis is at the end of the formula itself.
The extra open parenthesis in the AND function is basically taking everything after that and including it in your AND function. The rest of the formula is not laid out in the logical statement syntax that the AND function requires, and that is why you are getting that particular error message.
-
Dear Paul,
Thank you for your reply and support, I have then rechecked all my parenthesis, and I have came up with this:
=IF($[Actual Payment Week]@row = [2019 Wk 01]$2, $[Exact Amount Received]@row, IF(AND(NOT(ISBLANK($[Actual Payment Week]@row))), $[Expected Payment Week]@row = [2019 Wk 01]$2, ""), IF($[Expected Payment Week]@row = [2019 Wk 01]$2, $[Invoice Value]@row, ""))
However, I noticed ISBLANK was underlined, so probably the logical expression was wrong, so I adjusted with <>"" instead:
=IF($[Actual Payment Week]@row = [2019 Wk 01]$2, $[Exact Amount Received]@row, IF(AND($[Actual Payment Week]@row <> "", $[Expected Payment Week]@row = [2019 Wk 01]$2), ""), IF($[Expected Payment Week]@row = [2019 Wk 01]$2, $[Invoice Value]@row, ""))
But still, Incorrect Argument Set is returned.
Please see pictures attached showing the parenthesis colors are now well placed, however I still do not understand what is wrong.
Let me know if you see something else on your side,
Best,
Michaël -
You are closing out your second IF statement before starting the third.
Your's:
=IF($[Actual Payment Week]@row = [2019 Wk 01]$2, $[Exact Amount Received]@row, IF(AND($[Actual Payment Week]@row <> "", $[Expected Payment Week]@row = [2019 Wk 01]$2), ""), IF($[Expected Payment Week]@row = [2019 Wk 01]$2, $[Invoice Value]@row, ""))
.
Adjusted:
=IF($[Actual Payment Week]@row = [2019 Wk 01]$2, $[Exact Amount Received]@row, IF(AND($[Actual Payment Week]@row <> "", $[Expected Payment Week]@row = [2019 Wk 01]$2), "", IF($[Expected Payment Week]@row = [2019 Wk 01]$2, $[Invoice Value]@row, "")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!