Best Of

The format for an if() function is:

=if(Test, Value if True, Value if False)

Your test section was written as: INDEX({CT Certs Range 1}, MATCH([Instructor Name]@row, {CT Certs Range 1}, 0))

This tells Smartsheet to index through your {CT Certs Range 1} by a variable number of rows (in this case, by the number of rows necessary to find the name from [Instructor Name]@row). What is DOESN'T do is give any test to be evaluated as true or false. Smartsheet finds some names, indexes through them and chooses the first, but then your "test" is just that name… hence, trying to decide whether "Frank" is true or false.

For the test section you need to include a comparison operator (the official documentation is quite poor on this, btw). Here is the list for Excel (there is no official list for Smartsheet):

So, for your test you need to use one of these so it could evaluate to either true or false. You could have done something like:

=IF(INDEX({CT Certs Range 1}, MATCH([Instructor Name]@row, {CT Certs Range 1}, 0))=[Instructor Name]@row, "yes", "no")

and it would likely have also worked. In this case, the formula is saying "Go do that indexing and grab the first name, if it matches with the name over in this other column consider it true and do the following…. otherwise….."

You see, it is the "if it matches…." bit that was missing before and that adding "=[Instructor Name]@row" to the "test" portion of the formula adds.

Alternatively, in the formula I recommended to you, you can see that I tell it to get the count but then the test is actually "if that count is greater than 0".

Outstanding! It works perfectly. :D

Thank you!

Does this work?

=IF(YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0) > 0, (YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0)) + " Y ") + (MONTH([Date Completed]@row) - MONTH([Date Completed]@row) + IF(MONTH([Date Completed]@row) < MONTH(Age@row), 12, 0)) + " M"