Looking for help with IF(HAS function. If value not found, then do a vlookup.

Hi, my formula continues to return as #INCORRECT ARGUMENT SET.

I want the cell to return a value of ZERO if the referenced cell HAS the word Closure. If it doesn't then I want it to do a vlookup.

Here is my current formula returning the error:

=IF(HAS([Project Type]@row, "Closure", 0, VLOOKUP([File ID]@row, {TM Completed Transactions KPIs (workflow s Range 1}, 4, false)))

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    edited 01/30/24 Answer ✓

    Hi @abaker,

    Your syntax looks incorrect. Also, the range you are using in the VLOOKUP formula looks off - "{TM Completed Transactions KPIs (workflow s Range 1}", I left it out of the formula below.

    This is what it should like: =IF(HAS([Project Type]@row,"Closure"),"0",VLOOKUP([File ID]@row, lookup_table, 4, false))

    Hope this helps,

    Dave

Answers

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭

    Hello @abaker,

    It looks like the formula just needs to be cleaned up a bit with the parentheses:

    =IF(HAS([Project Type]@row, "Closure"), VLOOKUP([File ID]@row, {TM Completed Transactions KPIs (workflow s Range 1}, 4, false))

    If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!

    Monique Odom

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    edited 01/30/24 Answer ✓

    Hi @abaker,

    Your syntax looks incorrect. Also, the range you are using in the VLOOKUP formula looks off - "{TM Completed Transactions KPIs (workflow s Range 1}", I left it out of the formula below.

    This is what it should like: =IF(HAS([Project Type]@row,"Closure"),"0",VLOOKUP([File ID]@row, lookup_table, 4, false))

    Hope this helps,

    Dave

  • @Dkazatsky2 this did the trick, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!