Help with correct formula please, currently IF/AND/HAS but maybe need a VLOOKUP?

I need help with this formula/a new formula. Columns 1-4 would be in a different sheet as a master file, and I'd like the formula to do what it is doing here with a cross-sheet reference. I've managed to figure out the cross sheet reference whilst testing formulas but I have to do a separate formula per row, is there a better/easier way to do this?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!