Pulling information from second sheet based off one criteria and and if it falls between two numbers
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"
Answers
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!