Function Problems

How to Calculate Total Cost Using VLOOKUP Across Two Data Tables in Excel

Guest Writer Oct 7, 2026 869 views

Question details

The user needs to calculate the total cost by matching an item to its corresponding unit price in a separate data table and multiplying it by the number of units.

How to Calculate Total Cost Using VLOOKUP Across Two Data Tables

Product: Excel
Device & OS: not provided
Scenario: Calculating total inventory or order costs using a primary table with quantities and a secondary table containing a master price list.
Observed behavior: The goal is to automatically retrieve unit prices from a secondary table and multiply them by quantities in the primary table to output the total cost in a single formula.

Before you start

Ensure that the item names in your primary table exactly match the names in your lookup table, as extra spaces or typos will cause the formula to return an error.

Solution 1Recommended

Use VLOOKUP Combined with Multiplication

Combine the VLOOKUP function with a multiplication operation to find the unit price and calculate the total cost in a single cell.

By nesting a VLOOKUP formula within a basic math operation, you can fetch data from a secondary table (like a price list) and instantly multiply it by the quantity in your current table. The formula structure typically looks like `=Quantity * VLOOKUP(Item, PriceTable, ColumnIndex, FALSE)`.

In the specific scenario provided, a negative multiplier is used (`-D5`), which is often applied in accounting contexts to denote an expense or deduction.

Select the target cell

Click on the cell where you want the total cost to be displayed, such as cell E5.

Enter the formula

Type the formula `=-D5*VLOOKUP(C5,H:I,2,FALSE)` into the formula bar. In this example, D5 represents the quantity (made negative for expenses), C5 is the lookup item, H:I represents the columns of your price list table, and 2 tells Excel to pull the price from the second column of that range.

Copy the formula down

Press Enter to calculate the first row. Then, click on cell E5, hover over the small square at the bottom-right corner to reveal the fill handle, and drag it down to apply the calculation to the remaining rows.

Use VLOOKUP Combined with Multiplication

Exact Match Requirement: The 'FALSE' argument at the end of the VLOOKUP formula ensures that Excel only returns a value if it finds an exact match for your item. If no match is found, it will return an #N/A error.

Seamless Formula Calculations with WPS Office

Easily Manage Data Tables and VLOOKUP Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup and reference formulas, allowing you to easily cross-reference data across multiple tables and calculate costs quickly.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your two data tables.
2. Input the VLOOKUP formula: Select the target cell, type your formula (e.g., `=-D5*VLOOKUP(C5,H:I,2,FALSE)`), and press Enter.
3. Drag to fill: Use the fill handle in the bottom-right corner of the cell to drag the formula down your entire column.

100% compatible with Microsoft Excel formulas, including VLOOKUP and XLOOKUPBuilt-in function reference and formula evaluation tools for easy troubleshootingFree, lightweight, and fast spreadsheet solutionFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error when calculating costs?

This error occurs when the lookup item in your primary table does not exactly match any item in your price table. Check for typos, extra spaces, or hidden characters in both tables. You can use the TRIM function to remove accidental trailing spaces.

How do I calculate costs if my unit prices are on a different worksheet?

You can reference another sheet in your VLOOKUP formula by navigating to that sheet while selecting your table array. Your formula will look something like `=D5*VLOOKUP(C5, Sheet2!A:B, 2, FALSE)`.

Can I use XLOOKUP instead of VLOOKUP for this calculation?

Yes, if you are using a version of Excel or WPS Spreadsheet that supports XLOOKUP, you can use `=D5*XLOOKUP(C5, H:H, I:I)`. This eliminates the need to count column index numbers and is generally more flexible.

Excel & Data Problems