# Excel to Smartsheet formula conversion error

Options

Hello All,

I am trying to import Scaled Agile Team Event calendar excel onto Smartsheet.

I am facing difficulty in automating Column F - Team Events on Smartsheet.

If someone can help me debug the query to work in Smartsheet.

I am attaching the excel from Scaled Agile and also pasting the query down here.

Excel Column Team Events - F12 (Working Query)

="Iteration " & LOOKUP(2,1/(\$C\$21:\$C\$27<=G12)/(\$D\$21:\$D\$27>=G12),\$B\$21:\$B\$27)&"."&NUMBERVALUE((G12-(LOOKUP(2,1/(\$C\$21:\$C\$27<=G12)/(\$D\$21:\$D\$27>=G12),\$C\$21:\$C\$27)))/(7*\$B\$7)+1)&IF((NUMBERVALUE((G12-(LOOKUP(2,1/(\$C\$21:\$C\$27<=G12)/(\$D\$21:\$D\$27>=G12),\$C\$21:\$C\$27)))/(\$B\$7*7)+1))=\$B\$6," IP","")

Smartsheet Import Adapted Query (Not working / #UNPARSEABLE) -

="Iteration "&LOOKUP(2,1/(\$[Column3]\$20:\$[Column3]\$26<=[Column7]17)/(\$[Column4]\$20:\$[Column4]\$26>=[Column7]17),\$[Column2]\$20:\$[Column2]\$26)&"."&_XLFN.NUMBERVALUE(([Column7]17-(LOOKUP(2,1/(\$[Column3]\$20:\$[Column3]\$26<=[Column7]17)/(\$[Column4]\$20:\$[Column4]\$26>=[Column7]17),\$[Column3]\$20:\$[Column3]\$26)))/(7*\$[Column2]\$6)+1)&IF((_XLFN.NUMBERVALUE(([Column7]17-(LOOKUP(2,1/(\$[Column3]\$20:\$[Column3]\$26<=[Column7]17)/(\$[Column4]\$20:\$[Column4]\$26>=[Column7]17),\$[Column3]\$20:\$[Column3]\$26)))/(\$[Column2]\$6*7)+1))=\$[Column2]\$5," IP","")

• ✭✭✭✭
Options

& doesn't work for concatenation in Smartsheet. Use + instead. Also, Smartsheet doesn't have a LOOKUP function. You can try VLOOKUP instead or INDEX-MATCH.

Functions List | Smartsheet Learning Center

• Options

Hey Anupriya,

Its gives me an error "Invalid Operation" when I run the below query based on your inputs

="Iteration " + VLOOKUP(2, 1 / ([Column3]20:[Column3]26 <= [Column7]11) / ([Column4]20:[Column4]26 >= [Column7]11), [Column2]20:[Column2]26) + "." + VALUE(([Column7]11 - (VLOOKUP(2, 1 / ([Column3]20:[Column3]26 <= [Column7]11) / ([Column4]20:[Column4]26 >= [Column7]11), [Column3]20:[Column3]26))) / (7 * [Column2]6) + 1) + IF((VALUE(([Column7]11 - (VLOOKUP(2, 1 / ([Column3]20:[Column3]26 <= [Column7]11) / ([Column4]20:[Column4]26 >= [Column7]11), [Column3]20:[Column3]26))) / ([Column2]6 * 7) + 1)) = [Column2]5, " IP", "")

Is there something I am missing ?

• Options

Index Match Example -

=INDEX([Column]:[Column], MATCH(DATE(YYYY, DD, MM), [Column]:[Column]))

How do I embed this in my current formula to work ?

• Employee
Options

Smartsheet and Excel have different functions and formula structures; you won't be able to copy/paste a formula from one type of sheet into the other, or replace one function with another. The formula will need to be re-created and re-written in Smartsheet.

That said, I'm not quite sure I understand what it is your formula is looking to do. Would you mind explaining the calculation that you're looking to do in a written description?

(As a side-note, it looks like Anupriya didn't see your response. You may want to @mention members in Community if you want to send them a notification).

Thanks!

Genevieve