Help with a formula based on conditions



I need assistance with creating a formula for the following:

  1. In the Annual $ cell, I would like the salary to automatically appear when two conditions have been met: the user provided the region (either US, CEMEA, LATAM, ASIA) AND the user has provided the Job Level.
  2. The salary should come from a different sheet (I have titled it "Reference" sheet).

First sheet:

Reference Sheet:

How can I bring the salary to the first sheet based on the region provided and the job level? Does somebody have a formula to meet this need? ex. someone at the USA in a 32-Sr Manager level would make $42, 254.



Best Answer

  • Lachlan Stead
    Lachlan Stead ✭✭✭✭✭✭
    Answer ✓

    There are a few options but it may be faster to move from a matrix to a listing - eg. level "60 - VP" would end up with 4 relevant lines. You could leave as single column with a combined description or have two columns and then a hidden column on each sheet with the role and region combined... your lookup would then be based on the hidden column value.

    Not sure if that helps?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!