Pulling information from second sheet based off one criteria and and if it falls between two numbers

Smartsheetuser222

I have one sheet that has job title, salary and then a column called Thirds location where I want to pull in information from another sheet and have it say lower third, middle third or upper third. On the other sheet I have the salary ranges and then broken into thirds. For example, For sally Gold, I want to reference the other sheet and have it identify the job title is coordinator and inbetween 60,000 and 63,333 so it would return "lower third"

Kelly Moore

Hello @Smartsheetuser222

Try this

=IF(COUNTIFS({Source Sheet Primary Column}, [Job Title]@row, {Source Sheet Salary Min}, @cell <= [Column3]@row, {Source Sheet Lower Third}, @cell >= [Column3]@row) > 0, "Lower Third", IF(COUNTIFS({Source Sheet Primary Column}, [Job Title]@row, {Source Sheet Lower Third}, @cell <= [Column3]@row, {Source Sheet Upper Third}, @cell >= [Column3]@row) > 0, "Middle Third", IF(COUNTIFS({Source Sheet Primary Column}, [Job Title]@row, {Source Sheet Upper Third}, @cell <= [Column3]@row, {Source Sheet Salary Max}, @cell >= [Column3]@row) > 0, "Upper Third")))

Expect that your ranges will have different names unless you rename them. If you have questions on cross sheet references, the link at the bottom may be helpful. Shout out if you have any questions.

Will this work for you?

Kelly

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references