Trying to return text in a cell based on meeting two criteria
I seem to be stuck. I have searched for answers in this forum and tried many formulas only to get errors. Think it probably a simple answer but can't seem to find it.
I have a sheet that scores our programs on a scale. I am trying to return the score based on the state and the project name. So for example below (this was just one of the many ) I was looking for "Alabama" then the program "Regional Onboarding Center ROC", then return the score that was entered in the cell from that same row.
=IF(AND({State} = "Alabama", {Project Name} = "Regional Onboarding Center ROC", {score}))
I tried countifs and ifs and index. I appreciate any advice.
Answers
-
Unfortunately you are trying to get the If function in the wrong method.
Your IF statement is saying "If State = Alabama and Project = ROC then show me "The Score".. the problem is the "The Score" is a column.. You need to tell it which row in the column you want..
You need to use a VLOOKUP or Index Match.. Those can get a bit confusing with And statements..
So the way I would approach this is
- create helper columns in both tables.. where you join the two values.
- By this I mean a column that would contain a formula like "=[State]+[Project Name]"
- This way you are looking up for an exact match "AlabamaRegional Onboarding Center ROC" vs "MichiganRegional Onboarding Center ROC"
- Then do a Vlookup from the One Table for "AlabamaRegional Onboarding Center ROC" and return the score
That's where VLOOKUP and INDEX MATCH come into play..
Have a read of this
https://help.smartsheet.com/function/vlookup
Feel free to comment if you need more help
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
- create helper columns in both tables.. where you join the two values.
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!