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.


  • Brent Wilson
    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

    Feel free to comment if you need more help

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!