How To Use VLOOKUP Between Two Worksheets | VLOOKUP In Excel

If you’re managing data like material lists, tender comparisons, or subcontractor pricing, Excel’s VLOOKUP function is about to become your best friend. It’s an easy way to pull data from one sheet to another, saving you hours of manual searching. Let’s dive into how you can use VLOOKUP to streamline your workflows.

What Is VLOOKUP?

VLOOKUP stands for Vertical Lookup. It’s a function that helps you find information in a table and return related data. For example, you can look up a material code and retrieve the corresponding price, supplier, or specification.

So how can it helps us as Quantity Surveyors. It can do many things but to give some examples, it can;

Retrieve unit rates for materials or labour from price books.
Compare tender submissions by pulling data from multiple sheets.
Match material descriptions with supplier rates.
Generate cost summaries from BOQs and rate libraries.

Structure

Lets look and how a VLOOKUP formula should be structured. Here we have the formula…

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let’s break it down:

lookup_value: The item you want to find (e.g., a material code).
table_array: The range where Excel will search (e.g., a rate library).
col_index_num: The column number in the table that contains the data you need.
[range_lookup]: Use FALSE for an exact match (important for QS work).

Scenario

Imagine you’re preparing a cost estimate.
• Sheet1 is where your BOQ is which includes items, quantities, and codes.
• And Sheet2 is your rate library. This lists material codes, descriptions, and unit rates.

Step 1: Setting Up Your Data

Ensure your BOQ and rate library have a common column, like a material code. This will act as the lookup value.

Step 2: Write the VLOOKUP Formula

In Sheet1, click the cell where you want the rate to appear. Use this formula:

VLOOKUP(A2, Sheet2!$A$2:$C$100, 3, FALSE)

Here’s what it does:

A2: Looks up the material code in Sheet1.
Sheet2!$A$2:$C$100: Searches for the code in the rate library (Sheet2).
3: Retrieves data from the 3rd column (unit rate).
FALSE: Ensures an exact match.

Step 3: Copy the Formula Down

Drag the formula down to apply it to all BOQ items. Excel will automatically adjust the lookup value for each row.

Step 4: Fix the Table Array

To prevent the table array from shifting when copying the formula, lock it with dollar signs ($):

=VLOOKUP(A2, Sheet2!$A$2:$C$100, 3, FALSE)

Common VLOOKUP Errors

#N/A Error
o This means the lookup value isn’t in the table.
o And how do you fix it. Ensure the material code or identifier exists in both sheets.
#REF! Error
o This means the column index number is too high for the table array.
o And how do you fix it. Double-check the range and adjust the index number.
Wrong Results
o This means Excel may be using an approximate match instead of an exact match.
o To fix it, ensure you always use the “FALSE” statement for exact matches.

Tips for Using VLOOKUP in QS Work

• Keep Data Clean: Eliminate duplicates and ensure consistent formatting for codes or identifiers.
• Explore XLOOKUP: If you have Excel 365 or 2021, consider using XLOOKUP, which is more versatile and doesn’t require column index numbers. We’ll run through this in a future video.

Conclusion

VLOOKUP is a powerful tool for Quantity Surveyors, whether you’re pricing BOQs, comparing tenders, or managing cost data. With just a few clicks, you can save hours of manual work and focus on what really matters: delivering accurate, efficient estimates

Introducing Metroun Learning

Metroun Learning is an online education platform, specific to Quantity Surveyors which can satisfy all your formal CPD needs. We offer over 50 hours of formal CPD, with new courses added each month. Although all courses amass to 50 hours of formal CPD, each individual course has been engineered so it can be completed during your lunch break. This can save you time, money & reduce your carbon footprint. Metroun Learning will not only satisfy your formal CPD needs, but you’ll also gain access to: