VLOOKUP, ISNA, IF Statement

Someone2

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!

Rob Hagan

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

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

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

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.