Get Material Cost per Unit
I need to pull the unit cost for 'Concrete' and 'Lumber' from a vendor price list into my estimation sheet.
=VLOOKUP(A2,E2:G5,2,FALSE) How it works: VLOOKUP (Vertical Lookup) is used to search for a value in the first column of a table and return a corresponding value from another column in the same row. In this scenario, as a Construction Manager, you're using VLOOKUP to find a specific material (like 'Concrete' from cell A2) in your vendor price list (the range E2:G5) and retrieve its associated 'Unit Price' from the second column of that list. The `FALSE` argument ensures an exact match, preventing errors from partial matches, which is critical for accurate cost estimations and budgeting.
Data Setup
| Material | Quantity | Unit Cost | Item | Unit Price | Vendor | |||
|---|---|---|---|---|---|---|---|---|
| Concrete | 100 | Concrete | 75.00 | ABC Supply | ||||
| Lumber | 50 | Lumber | 12.50 | XYZ Lumber | ||||
| Steel Beams | 20 | Pipes | 5.00 | Plumbing Co. | ||||
| Steel Beams | 150.00 | Metal Works |
Step-by-Step Guide
Identify the material you want to look up (e.g., 'Concrete' in cell A2 of your estimation sheet).
Locate your vendor price list (e.g., in cells E2:G5), which contains the material names and their corresponding unit prices.
In the cell where you want the unit cost to appear (e.g., C2), type `=VLOOKUP(`.
Select the cell containing the material name you want to find (A2). This is your lookup_value.
Type a comma (`,`) and then select the entire range of your vendor price list, excluding headers (E2:G5). This is your table_array.
Type another comma (`,`) and enter the column number within your selected price list range that contains the unit price. In our example, 'Unit Price' is the 2nd column in the E2:G5 range, so enter `2`. This is your col_index_num.
Type a comma (`,`) and then type `FALSE` for an exact match. This is crucial to ensure you get the exact cost for 'Concrete', not something similar. This is your range_lookup.
Close the parenthesis `)` and press Enter. The unit cost for 'Concrete' will appear.
Drag the fill handle (the small square at the bottom-right of cell C2) down to apply the formula to other materials like 'Lumber' and 'Steel Beams' in your estimation sheet.
Explore More
Flag Budget Overruns for Materials
I want a formula that displays 'Over Budget' if the actual material cost exceeds the estimated cost, otherwise 'Within Budget'.
Count Safety Incidents by Site
I need to count the number of safety reports logged for 'Site A' to monitor compliance.
Calculate Total Hours by Subcontractor
I want to sum the total billable hours worked by the 'Plumbing' subcontractor across all project days.
Find Supplier Contact by Material
I need to find the phone number of the supplier who provides 'Drywall', searching through a messy vendor list.