Nested If/And/Or with Vlookup

Dana G
Dana G ✭✭
edited 06/22/22 in Formulas and Functions

Hello! I'm responsible for ensuring a large number of reports are submitted on time. To meet deadline, I'd like to adjust the due date to account for holidays and weekends.

I added all holiday dates to a separate "Holidays" column, and identified day of the week in a separate "Due Date Day" column using the formula =IF([Due Date]@row = "", "", WEEKDAY([Due Date]@row)).

My current attempt has two nested IF formulas. The first identifies if the date is in the Holiday column AND is a Sunday OR Monday, it will add a day to the due date. The second identifies if the date is in the Holiday column AND is a Friday OR Saturday, it will subtract a day from the due date.

=IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false), <>"#NO MATCH", OR([Due Date Day]@row = "1", [Due Date Day]@row = "2")), [Due Date]@row + 1, "", IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false), <>"#NO MATCH", OR([Due Date Day]@row = "6", [Due Date Day]@row = "7")), [Due Date]@row - 1, ""))

However, I'm receiving the error "#INCORRECT ARGUMENT SET"

Can you see where my formula needs to be adjusted?

Thanks for your help!!

Best Answer

  • Dana G
    Dana G ✭✭
    Answer ✓

    @Mike TV thank you! And I also realized I had an extra "," after my vlookup statements. I was able to get it working with this:

    =IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false) <>"#NO MATCH", OR([Due Date Day]@row = "1", [Due Date Day]@row = "2")), [Due Date]@row + 1, IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false) <>"#NO MATCH", OR([Due Date Day]@row = "6", [Due Date Day]@row = "7")), [Due Date]@row - 1, ""))

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Dana G

    =IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false), <>"#NO MATCH", OR([Due Date Day]@row = "1", [Due Date Day]@row = "2")),

    Getting up to this point closes out the AND function and with the comma at the end leaves you ready for your true value. However, right after that you're double quotes for the false value closes out the entire formula doesn't it? So that the next IF statement I believe is ignored possibly and is causing the failure?

  • Dana G
    Dana G ✭✭
    Answer ✓

    @Mike TV thank you! And I also realized I had an extra "," after my vlookup statements. I was able to get it working with this:

    =IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false) <>"#NO MATCH", OR([Due Date Day]@row = "1", [Due Date Day]@row = "2")), [Due Date]@row + 1, IF(AND(VLOOKUP([Due Date]@row, Holidays:Holidays, 1, false) <>"#NO MATCH", OR([Due Date Day]@row = "6", [Due Date Day]@row = "7")), [Due Date]@row - 1, ""))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!