How to Setup Formulas to Automatically Link & Pull Data from Other Sheets

I'd like to try and figure out how to most effectively and efficiently connect 2 separate smartsheets together, preferably through some sort of smart formula/code. I don't think doing a manual "cell linking" from a cell in one sheet to the next would be very smart or efficient for what I'm trying to do here.

I have both a "Budget" sheet and "Average Rates" sheet. "Budget" stores a template of the budget line items. "Average Rates" is essentially a database of hundreds of quotes I've done in the past that averages out the "low, mid, high" cost for each of the line items. Below is a hypothetical example of what I'm trying to do:

1. On the "Budget" sheet I have an expense item called "Camera Gear". I have some pictures attached to help show what the budget sheet looks like visually.
2. To the right, I have a column called "Rate" with a drop-down menu of 3 rate options: "Low Rate", "Mid Rate", "High Rate".
3. When I select a rate from the dropdown menu, I'd then want smartsheet to pull data from a cell in the "Average Rates" sheet where the rate data is stored. So if I were to select "Mid Rate" for the "Camera Gear" line item, I'd want smartsheet to go to the "Average Rates" sheet and pull data from a cell that stores the mid rate for camera gear.

Why not just store this quote data inside the "Budget" template? Why have it in a separate "Average Rates" sheet? Because I'll eventually have thousands of quotes stored in there and I don't want to clutter/slow down the performance of my normal budget sheet. I want to make the budget simple and easy for the client to review without having to manually delete/hide past quote line items before presenting the budget to them. 

So I guess the logic of this function would be some like this:

1. If "rate column cell" is equal to "x rate" inside the "budget sheet"
2. Go to "average rates sheet" and pull data from "x cell"

I know this might be a tricky one to accomplish, any guidance or ideas is much appreciated, thanks!