Compile error - expected end of statement

[2022]

Does anyone know why this formula would give a compile error (expected end of statement ) after the first space underscore " _" (third line below)?

"=IF(A32="""","""",IF(AND($N$26<>""CAD"",BR32=""TRUE""),ROUND(VLOOKUP(UPPER(CONCAT($BW$34,""_"",'Contract pg 1'!A32)),data_fin,8,0),2)/$B$4,IF(AND($N$15=""X11"",$AL$13=""ABC1"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,4,FALSE)/$B$4, _

IF(AND($N$15=""X11"",$AL$13=""ABC2"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,6,FALSE)/$B$4,IF(AND($N$15=""X11"",$AL$13=""ABC3"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,8,FALSE)/$B$4,IF(AND($AK$14=""X11"",$AL$13=""ABC1"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,4,FALSE)/$B$4, _

IF(AND($AK$14=""X11"",$AL$13=""ABC2"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,6,FALSE)/$B$4,IF(AND($AK$14=""X11"",$AL$13=""ABC3"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,8,FALSE)/$B$4,IF(AND($AK$14=""X22"",$AL$13=""ABC1"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,5,FALSE)/$B$4, _

IF(AND($AK$14=""X22"",$AL$13=""ABC2"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,7,FALSE)/$B$4,IF(AND($AK$14=""X22"",$AL$13=""ABC3"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,9,FALSE)/$B$4,IF(BP32=""TRUE"",VLOOKUP(UPPER(A32),NewList,2,FALSE)/$B$4, _

IF(BT32=""TRUE"",ROUND(VLOOKUP(UPPER(A32),data_list,3,0),2)/$B$4,"""")))))))))))))"

 

[RoryA]

You can't put the underscore inside the formula string. You need to terminate that line with a quote, ampersand and underscore, then start the next line with a quote:

"=IF(A32="""","""",IF(AND($N$26<>""CAD"",BR32=""TRUE""),ROUND(VLOOKUP(UPPER(CONCAT($BW$34,""_"",'Contract pg 1'!A32)),data_fin,8,0),2)/$B$4,IF(AND($N$15=""X11"",$AL$13=""ABC1"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,4,FALSE)/$B$4," & _
"IF(AND($N$15=""X11"",$AL$13=""ABC2"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,6,FALSE)/$B$4,IF(AND($N$15=""X11"",$AL$13=""ABC3"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,8,FALSE)/$B$4,IF(AND($AK$14=""X11"",$AL$13=""ABC1"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,4,FALSE)/$B$4," &  _
"IF(AND($AK$14=""X11"",$AL$13=""ABC2"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,6,FALSE)/$B$4,IF(AND($AK$14=""X11"",$AL$13=""ABC3"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,8,FALSE)/$B$4,IF(AND($AK$14=""X22"",$AL$13=""ABC1"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,5,FALSE)/$B$4," &  _
"IF(AND($AK$14=""X22"",$AL$13=""ABC2"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,7,FALSE)/$B$4,IF(AND($AK$14=""X22"",$AL$13=""ABC3"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,9,FALSE)/$B$4,IF(BP32=""TRUE"",VLOOKUP(UPPER(A32),NewList,2,FALSE)/$B$4," &  _
"IF(BT32=""TRUE"",ROUND(VLOOKUP(UPPER(A32),data_list,3,0),2)/$B$4,"""")))))))))))))"

 

[2022]

Ok, thank you. I’ll try that!

 

[2022]

You can't put the underscore inside the formula string. You need to terminate that line with a quote, ampersand and underscore, then start the next line with a quote:

"=IF(A32="""","""",IF(AND($N$26<>""CAD"",BR32=""TRUE""),ROUND(VLOOKUP(UPPER(CONCAT($BW$34,""_"",'Contract pg 1'!A32)),data_fin,8,0),2)/$B$4,IF(AND($N$15=""X11"",$AL$13=""ABC1"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,4,FALSE)/$B$4," & _
"IF(AND($N$15=""X11"",$AL$13=""ABC2"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,6,FALSE)/$B$4,IF(AND($N$15=""X11"",$AL$13=""ABC3"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,8,FALSE)/$B$4,IF(AND($AK$14=""X11"",$AL$13=""ABC1"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,4,FALSE)/$B$4," &  _
"IF(AND($AK$14=""X11"",$AL$13=""ABC2"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,6,FALSE)/$B$4,IF(AND($AK$14=""X11"",$AL$13=""ABC3"",BZ32=""TRUE""),VLOOKUP(UPPER(A32),XORange,8,FALSE)/$B$4,IF(AND($AK$14=""X22"",$AL$13=""ABC1"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,5,FALSE)/$B$4," &  _
"IF(AND($AK$14=""X22"",$AL$13=""ABC2"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,7,FALSE)/$B$4,IF(AND($AK$14=""X22"",$AL$13=""ABC3"",CB32=""TRUE""),VLOOKUP(UPPER(A32),XORange,9,FALSE)/$B$4,IF(BP32=""TRUE"",VLOOKUP(UPPER(A32),NewList,2,FALSE)/$B$4," &  _
"IF(BT32=""TRUE"",ROUND(VLOOKUP(UPPER(A32),data_list,3,0),2)/$B$4,"""")))))))))))))"

That worked! Thank you!