Trying to return text in a cell based on meeting two criteria

Elguapo24

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.

Brent Wilson

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

1. create helper columns in both tables.. where you join the two values.
   1. By this I mean a column that would contain a formula like "=[State]+[Project Name]"
   2. This way you are looking up for an exact match "AlabamaRegional Onboarding Center ROC" vs "MichiganRegional Onboarding Center ROC"
2. 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