How to use IF Statement with INDEX/MATCH

Options
ycox
ycox
edited 03/27/24 in Formulas and Functions

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)

Best Answer

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    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")
    

    ...

  • Katy H
    Katy H ✭✭✭✭✭✭
    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

    Head of Product Management

    ILLA Canna

    LinkedIn

  • ycox
    Options

    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
    heyjay ✭✭✭✭✭
    Answer ✓
    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.

    ...

  • ycox
    ycox
    edited 04/02/24
    Options

    @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
    heyjay ✭✭✭✭✭
    Options

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

    ...

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

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

    Like this?

    ...

  • ycox
    ycox
    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!