How to use IF Statement with INDEX/MATCH
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
-
@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.
...
Answers
-
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")
...
-
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)
-
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.
-
@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.
...
-
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.
-
Im having a hard time imagining that one. Can you provide a screenshot of the sheet?
...
-
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...
-
Facilitator to Participant Ratio = IF([No Changes box]@row = 1, [Helper Column Facilitator Ratio]@row, "")
Like this?
...
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!