Matching multiple values from multiple columns in linked sheet to fill cell with content from third

ON ✭✭
edited 12/09/19 in Formulas and Functions

I have Two sheets, one that acts as a "Rate card", it is broken down into Job title / Location / Day rate and the second is a budget sheet.

For Example, Rate Card

Column 1 / Column 2 / Column 3

Project Manager / UK / £10

Project Manager / US / £20

Developer / UK / £11

Developer / US / £22

In my Budget sheet I have a Resource Column, Loc, Job title, Day Rate. The location and Job title are automatically populated based on a formula that matches the resource name from a master resource sheet and returns the relevant value. What I would like is a formula that allows me to match multiple criteria (Location (from a range) and Job title (from a range)) and then fill the specified cell with the value from a third range (Day rate).

Example: In the Budget sheet, if location is "UK" and matches value in the "linked sheet range 1" and Job Title is "Developer" and matches "linked sheet range 2" I would like to return the value "£11" from "linked sheet range 3".

Any thoughts would be appreciated.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!