IF INDEX MATCH yielding INVALID DATA or NO MATCH

SharpC

I am creating a formula to look at an instructor's name on one sheet and see if it exists on another sheet (verifying that a qualification has been completed). My formula sort of works, if there's a match it tells me "INVALID DATA TYPE" and if there's no match it says "NO MATCH"

I was hoping it would check a box and originally had that format on the column until these errors. I'd also be content with a simple yes/no. All columns in question are formatted as text/number.

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

CT Certs Range 1 is the Instructor Name column on the sheet being searched.

How can I fix this?

Jgorsich

You never completed the logical test in your if() statement, right now you are saying are telling it to index to a person's name but not telling it what to do as far as a logical test goes. "=if ( "Frank", "yes", "no")"…. is Frank true or false?

I'd recommend the following instead:

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

here you are saying "=if( "Frank" shows up more than 0 times, "yes", "no")"

Also, just fyi, I'd do it as a countifs rather than a simple countif because you may want to add more criteria later and the difference in performance is unlikely to be noticed. But, for the record, countif() would actually be marginally better than countifs().

Jgorsich

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".

Jgorsich

You never completed the logical test in your if() statement, right now you are saying are telling it to index to a person's name but not telling it what to do as far as a logical test goes. "=if ( "Frank", "yes", "no")"…. is Frank true or false?

I'd recommend the following instead:

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

here you are saying "=if( "Frank" shows up more than 0 times, "yes", "no")"

Also, just fyi, I'd do it as a countifs rather than a simple countif because you may want to add more criteria later and the difference in performance is unlikely to be noticed. But, for the record, countif() would actually be marginally better than countifs().

SharpC

Thanks! That got the result I was looking for and I really appreciate the suggestion.

I don't understand what I was missing from the original formula, to me it reads "If there is a match between the given cell and the range on the other sheet, then return yes, otherwise return no." What operator/condition/argument is missing from the formula? And why did it give me errors still in accordance with what I was looking for? I'm relatively new to smartsheets, so I'm just trying to understand more.

Thanks again!

Jgorsich

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".

SharpC

Thank you for that!

I think I was misunderstanding what the INDEX/MATCH operators were doing. I appreciate the time you took to clarify and educate.