Nested IF or MATCH or JOIN+MATCH ?

Michaël @ NDA Group

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

Paul Newcome

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.

Michaël @ NDA Group

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

 

Paul Newcome

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, "")))