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

ON

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.

Paul Newcome

Yes. Try something along the lines of this...

 

=JOIN(COLLECT({Range 3}, {Range 1}, [Country Column]@row, {Range 2}, [Title Column]@row))

ON

LEGEND!!!! That works a treat! I cannot tell you how happy that just made me... It's been bugging me for days. Time to put the computer down and rejoin humanity!

Thank you!