Formula to populate field from another sheet using multiple criteria

Shannon L. · 10/18/24

Hi there,

In one sheet ("pay scale") I have our employee pay scale and in another ("Employee Salary") I have our employees listed with their experience and education levels. Is there a Smartsheet formula that I can use to pull in their salary based on their experience and education levels? For example, if they have a BA + 15 and 10 years experience, their salary is 546.00.

"Pay Scale"

"Employee Salary"

Thank you!!

MarceHolzhauzen · 10/18/24

Hi @ShannonL

If you only have core app functionality, you will not be able to do multi dimensional lookups.
Here's a previous thread on it: index match with a set of data (2 dimensional match)

You can however achieve this with one of a few formulas - it will be quite LOOOONNGGGG though.

Here's what the first part of the formula will look like- using this to explain the formula and will then populate the full formula. The "next" part of this forumala will be replaced by the same formula for each column.

=IF(Experience@row = "BA", INDEX(COLLECT({Pay Scale Sheet _BA Column}, {Pay Scale_Experience Column}, Prep@row), 1),"next")

Explanation:

IF(Experience@row = "BA", ...):
- This checks if the value in the Experience@row column is "BA". If true, it proceeds to execute the INDEX and COLLECT functions.
COLLECT({Pay Scale Sheet _BA Column}, {Pay Scale_Experience Column}, Prep@row):
- This collects the values from the BA Column in the Pay Scale Sheet, where the value in the Experience Column matches Prep@row.
- Ensure that Prep@row correctly references the desired criterion in the "Experience" column.
INDEX(..., 1):
- The INDEX function returns the first matching result from the values collected by the COLLECT function. If multiple rows match, it will return the first result.
"next":
- If the condition (Experience@row = "BA") is not met, the formula returns the string "next".

Purpose:

If the employee's experience is "BA", the formula collects and retrieves the first matching compensation or value from the Pay Scale Sheet based on their preparation level.
If the experience is not "BA", the formula returns "next" as a placeholder or next step indicator.

Completed Formula:

=IF(Experience@row = "BA", INDEX(COLLECT({Pay Scale Sheet _BA Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "BA+15", INDEX(COLLECT({Pay Scale Sheet _BA+15 Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "BA+45", INDEX(COLLECT({Pay Scale Sheet _BA+45 Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "BA+90", INDEX(COLLECT({Pay Scale Sheet _BA+90 Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "MA", INDEX(COLLECT({Pay Scale Sheet _MA Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "MA+45", INDEX(COLLECT({Pay Scale Sheet _MA+45 Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "MA+90", INDEX(COLLECT({Pay Scale Sheet _MA+90 Column}, {Pay Scale_Experience Column}, Prep@row), 1),

IF(Experience@row = "PhD", INDEX(COLLECT({Pay Scale Sheet _PhD Column}, {Pay Scale_Experience Column}, Prep@row), 1))

All that being said, it might be better to change your Pay Scale sheet to look like this:

Your formula will be a lot more stable and easier to scale if you add years and education levels

If you use this method, your formula will be

=INDEX(COLLECT({New Pay Scale Sheet_Compensation Column}, {New Pay Scale_Education Level Column}, Experience@row,{New Pay Scale_Experience Column}, Prep@row),1)

Explanation:

COLLECT:
- {New Pay Scale Sheet_Compensation Column}: This specifies the column from which you want to retrieve data (likely the compensation value).
- {New Pay Scale_Education Level Column}, Experience@row: Filters the data based on the employee's education level, comparing it with the value in Experience@row.
- {New Pay Scale_Experience Column}, Prep@row: Further filters the data based on the preparation or experience level, matching it with Prep@row.
INDEX(..., 1):
- Retrieves the first matching result from the data collected by COLLECT. If multiple rows match, it will return the first one.

This formula will return the compensation from the new pay scale sheet based on the employee's education level and preparation level.

Hope this helps

Protonsponge · 10/18/24

Hello @ShannonL,

You could use INDEX, MATCH to achieve what you are looking for here I believe. I have made a demo of how I would set this up if I was doing it below. There is a useful help article on cross sheet references here in case its of any use to you - LINK

The general formula would be:-

=INDEX({Pay Scale_BA}, MATCH(Prep@row, {Pay Scale_Experience}), 0)

You could set this up for each row for in your Employee Salary cross sheet reference highlighted in bold above for each education level column.

It may be over complicating things but if you wanted to set this up as a column formula and have your [Experience] and [Prep] columns as drop down boxes, you could build a series IF statements in to the formula. If that's of interest, I could share some screen shots as a demo to help.

I hope that is helpful yo you in someway,

Protonsponge

MarceHolzhauzen · 10/18/24

Hi @ShannonL

If you only have core app functionality, you will not be able to do multi dimensional lookups.
Here's a previous thread on it: index match with a set of data (2 dimensional match)

You can however achieve this with one of a few formulas - it will be quite LOOOONNGGGG though.

Here's what the first part of the formula will look like- using this to explain the formula and will then populate the full formula. The "next" part of this forumala will be replaced by the same formula for each column.

=IF(Experience@row = "BA", INDEX(COLLECT({Pay Scale Sheet _BA Column}, {Pay Scale_Experience Column}, Prep@row), 1),"next")

Explanation:

IF(Experience@row = "BA", ...):
- This checks if the value in the Experience@row column is "BA". If true, it proceeds to execute the INDEX and COLLECT functions.
COLLECT({Pay Scale Sheet _BA Column}, {Pay Scale_Experience Column}, Prep@row):
- This collects the values from the BA Column in the Pay Scale Sheet, where the value in the Experience Column matches Prep@row.
- Ensure that Prep@row correctly references the desired criterion in the "Experience" column.
INDEX(..., 1):
- The INDEX function returns the first matching result from the values collected by the COLLECT function. If multiple rows match, it will return the first result.
"next":
- If the condition (Experience@row = "BA") is not met, the formula returns the string "next".

Purpose:

If the employee's experience is "BA", the formula collects and retrieves the first matching compensation or value from the Pay Scale Sheet based on their preparation level.
If the experience is not "BA", the formula returns "next" as a placeholder or next step indicator.