# How to use IF Statement with INDEX/MATCH

Options
edited 03/27/24

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)

• ✭✭✭✭✭
Options

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

...

• ✭✭✭✭✭
Options

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")
```

...

• ✭✭✭✭✭✭
Options

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)
```

Katy Hall

ILLA Canna

• Options

Thank you @heyjay

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

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.

• ✭✭✭✭✭
Options

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

...

• edited 04/02/24
Options

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.

• ✭✭✭✭✭
Options

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

...

• Options

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

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

Like this?

...

• edited 04/03/24
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!