Hi team, may I have help with the IF formula below?

knc1008a

=IF([Rank]@row = "1", [Request Date]@row + 5,""), IF([Rank]@row = "2", [Request Date]@row + 7,""), IF([Rank]@row = "3", [Request Date]@row + 9,""), IF([Rank]@row = "4", [Request Date]@row + 11,""), IF([Rank]@row = "#NO MATCH", [Request Date]@row + 11, "")))))

I'm trying to add an IF formula based on the rank column and create a due date depending on the rank column. However, I'm receiving the "UNPARSEABLE" error.

Thank you for any help!

VBAGuru

You only need the final: , "" and also, when you are looking at a #, then quotes around the number are not needed.

Try this:

=IF([Rank]@row = 1, [Request Date]@row + 5, IF([Rank]@row = 2, [Request Date]@row + 7, IF([Rank]@row = 3, [Request Date]@row + 9, IF([Rank]@row = 4, [Request Date]@row + 11, IF([Rank]@row = "#NO MATCH", [Request Date]@row + 11, "")))))

DKazatsky2

Hi @knc1008a,

Give this a try (untested).

=IF(Rank@row = 1, [Request Date]@row + 5, IF(Rank@row = 2, [Request Date]@row + 7, IF(Rank@row = 3, [Request Date]@row + 9, IF(Rank@row = 4, [Request Date]@row + 11, IF(Rank@row = "#NO MATCH", [Request Date]@row + 11, "")))))

Hope this helps,

Dave

knc1008a

Thank you, I tried both but still receiving "UNPARSEABLE".

=IF([Rank]@row = 1, [Request Date]@row + 5), IF([Rank]@row = 2, [Request Date]@row + 7), IF([Rank]@row = 3, [Request Date]@row + 9), IF([Rank]@row = 4, [Request Date]@row + 11), IF([Rank]@row = "#NO MATCH", [Request Date]@row + 11, "")))))

DKazatsky2

@knc1008a,

You are getting "UNPARSEABLE" because you are closing the IF statements - if you look at the examples we gave, we only have closing parens ")" at the very end of the formula, you have them throughout.

Remove those parens, only leave the ones at the end, and you should be good.

knc1008a

Thank you for your help. I removed the extra parenthesis. I'm getting the "invalid column value" error now. Is this error because the Rank column has a Vlookup formula in it? The Vlookup generates the Rank 1-4.

Mark.poole

@knc1008a

You are getting the error because of the '#no match' this is an error and not an actual column value. instead do an iferror to make the '#no match' be something else. Then you will be able to correct everything

Mark.poole

@knc1008a

Instead try this.

=IFERROR(IF(ISBLANK(Rank@row), "", IF(Rank@row = 1, [Request Date]@row + 5, IF(Rank@row = 2, [Request Date]@row + 7, IF(Rank@row = 3, [Request Date]@row + 9, IF(Rank@row = 4, [Request Date]@row + 11))))), [Request Date]@row + 11)

ALSO be sure to have the column that the formula is in be a date column this could also cause the invalid column value error

knc1008a

Thank you all for your quick responses! It is working now :)