How to create formula that compares 2 columns on 2 different sheets & returns a value


I need to figure out how to pull in the right dollar amount from another sheet into the main one. The $ amount will be based on 2 criteria:

  1. The deal type
  2. The number of doctors

The deal type is a column on both sheets, same with the number of doctors. So I need to compare those 2 columns on both sheets (helper sheet & main sheet), and have the formula bring in the corresponding $ amount onto the main sheet (SPIFF amount column). Below is the helper sheet. For instance, if the main sheet has a row with "CSA" and 14 doctors, I want the formula to key in $25,000 on my main sheet.

See below my main sheet. The $25,000 would populate in the SPIFF Amount column:

How can I accomplish this?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!