Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

VLOOKUP, ISNA, IF Statement

Options
Someone2
Someone2
edited 12/09/19 in Archived 2017 Posts

Hi!

 

I would like to know is this kind of formula available in smartsheet?

=IF(ISNA(VLOOKUP(C2;I:I;1;FALSE));"NO";"YES")

 

In short- If there is the same name in other collumn mark YES

 

Thnak you!

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    Hi Someone,

     

    Yes, there is that kind of formula in SmartSheet.

     

    If your column is a checkbox then the formula could be:

    =NOT(ISERROR(MATCH([Find this]2, [In this]:[In this], 0)))

     

    If you want those Yes/No answers then the formula could be:

    =IF(ISERROR(MATCH([Find this]2, [In this]:[In this], 0)), "No", "Yes")

     

    Personally, I prefer the former and the boolean result can be tested more robustly in other formulas (i.e. any misspelling yes or no is avoided).

     

    Cheers,

     

    Rob.

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    Hi again,

     

    Just realised that you may have a blank entry in your "Find this" field and wish to treat that as "not found" in the array. Thus,

    =AND([Find this]2 <> "", NOT(ISERROR(MATCH([Find this]2, [In this]:[In this], 0))))

     

    Cheers,

     

    Rob.

  • Someone2
    Someone2
    edited 02/13/17
    Options

    Hi, thank you for your reply.

    But the last formula is not working, error is  #INVALID COLUMN VALUE

     

     

    What could be the issue?

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    Hi Someone,

     

    The first formula must be in a column of type Checkbox as it returns a boolean value.

    The second formula must be in a column of type Text/Number as it returns text.

    My second post also requires a column of type Checkbox.

     

    Hope that resolves the issue.,

     

    Cheers,

     

    Rob.

     

     

This discussion has been closed.