# IF Formula

Options
✭✭

Setting up a formula where Tier I = 1500, Tier II = 1200, otherwise it's 1000. This formula setup works for me with in-sheet references, but not cross-sheet references... not sure how to adjust for the cross-sheet.

=IF({MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", IF({MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))

Thank you!

• ✭✭✭✭✭✭
Options

@aecross Not sure your problem description is complete. What is it in the sheet with the formula that matches to something in the cross-referenced sheet? You need to make that connection, don't you? You may need to build an index formula that connects the cross-ref range to the row of the sheet you are in.

dm

• ✭✭
Options

I've indexed this correctly and it pulls in the Tier level. My next question is how to incorporate the IF statement into an index formula? With the below i am getting an unparsable error.

=IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0), {MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", {MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))

• ✭✭✭✭✭✭
Options

@aecross You want your IF to do this: IF (<the INDEX statement, that returns some result> EQUALS <your condition of "Tier 1"> COMMA ...

I can see that the INDEX statement doesn't close properly. I think you need a closing bracket here (at the end):

IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0))

and then you need to evaluate what it returns against your condition.

dm

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!