How to use IF Statement with INDEX/MATCH

ycox

Can someone check the following formula? I am receiving an error message stating that the Column Formula Syntax is not quite right.

=IF([No Change]@row,(INDEX({Master CMP Reference Sheet AGD Codes}, MATCH([Master Course Code]@row, {Master Course Code Column}, 0)), 1), 0)

heyjay

https://community.smartsheet.com/discussion/comment/427568#Comment_427568

@ycox - that was just an example to complete the IF logic.

For check boxes use 1 and 0

=IF([No Change]@row = 0,

INDEX({Master CMP Reference Sheet AGD Codes}, 
MATCH([Master Course Code]@row, {Master Course Code Column},0))

,"")

If the box is uncheck, the do the INDEX MATCH otherwise leave it blank.

heyjay

Your IF Statement needs a complete logic, then you can use the INDEX MATCH for the 'if true' value.

=IF([No Change]@row = "Yes",

INDEX({Master CMP Reference Sheet AGD Codes}, 
MATCH([Master Course Code]@row, {Master Course Code Column},0))

,"Otherwise, If false here")

Katy H

With an IF statement, the first portion needs to contain a statement. So it depends on what you are wanting it to say.

I will propose a statement based on an assumption. If I want the IF statement to check a box when the value in the column No Change matches the value pulled by the INDEX/MATCH, this is how I would alter your formula.

=IF([No Change]@row=(INDEX({Master CMP Reference Sheet AGD Codes}, MATCH([Master Course Code]@row, {Master Course Code Column}, 0)), 1, 0)

ycox

Thank you @heyjay

The [No Change] column is a checkbox column so Yes and No are not appropriate.

@Katy Hall

The If Statement is IF the No Change Column is checked, then Match the AGD Codes using the Master Course Code from the Reference Sheet, or leave blank otherwise. The Index Match alone works correctly, but when I try to include the IF Statement for the No Changes column, the example I posted says not quite right, when I tried your option it returns unparseable.

heyjay

https://community.smartsheet.com/discussion/comment/427568#Comment_427568

@ycox - that was just an example to complete the IF logic.

For check boxes use 1 and 0

=IF([No Change]@row = 0,

INDEX({Master CMP Reference Sheet AGD Codes}, 
MATCH([Master Course Code]@row, {Master Course Code Column},0))

,"")

If the box is uncheck, the do the INDEX MATCH otherwise leave it blank.

ycox

@heyjay

Thank you so much; that worked. I have one final question. This information is being gathered in a helper column because the column that I would like to fill is a dropdown list. I don't know the next step in the formula to accomplish that. Do I need to give another IF statement and list each option in the drop down? There isn't a lot, so that wouldn't be that challenging.

heyjay

Im having a hard time imagining that one. Can you provide a screenshot of the sheet?

ycox

Sure

When the No Changes box is checked, I would like the "Facilitator to Participant Ratio" dropdown list column in this example to autofill with the data from the reference sheet in the Helper Column Facilitator Ratio.

Thank you for working through this with me...

heyjay

Facilitator to Participant Ratio = 
IF([No Changes box]@row = 1, 
[Helper Column Facilitator Ratio]@row, "")

Like this?

ycox

Are you suggesting to put the formula above in the Facilitator to Participant ratio column?

I thought adding a formula to a column with a dropdown list would cancel the function of the dropdown list? Or are you recommending to add the formula above nested within the original formula like this?

=IF([No Changes]@row = 1, [Helper Column Facilitator Ratio]@row, ""), INDEX({Master CE CMP Reference Sheet Range 5}, MATCH([Master Course Code]@row, {Master Course Code Column}, 0)), "") -This option returns incorrect syntax.