Function Problems

How to Multiply a Quantity by a Rate Based on Two Names in Excel

Elise Williams Oct 7, 2026 869 views

Question details

The user needs a formula to look up a corresponding rate based on a person's first and last name across two different tables, and multiply that rate by a specified quantity.

How to Multiply a Quantity by a Rate Based on Two Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating total values or costs by dynamically matching specific individuals (combining their first and last names) to their assigned rates and multiplying by a volume or quantity.
Observed behavior: The user wants a streamlined formula to look up multiple criteria (first and last name) from a reference table and directly return a calculated product of the matched rate and a quantity.

Before you start

Ensure that the first and last names in your source table exactly match the names in your rate table, without any extra spaces. Also, verify that your version of Excel supports dynamic array functions like XLOOKUP.

Solution 1Recommended

Use XLOOKUP with Concatenated Criterias

The most efficient way to look up values based on multiple conditions is to use the XLOOKUP function with the ampersand (&) operator to concatenate the names.

By concatenating the first and last name references in your formula, Excel evaluates them as a single continuous string. You must also concatenate the corresponding lookup arrays (columns) so the conditions perfectly align.

Select the destination cell

Click on the cell in your quantity table where you want the calculated result (Rate * Quantity) to appear.

Enter the XLOOKUP formula

Assuming the first name is in F2, last name is in G2, and quantity is in H2. Also assuming your reference table has first names in A2:A4, last names in B2:B4, and rates in C2:C4. Type: =XLOOKUP(F2&G2,$A$2:$A$4&$B$2:$B$4,$C$2:$C$4)*H2

Apply and fill down

Press Enter to see the calculated result. Click the bottom-right corner of the cell and drag it down to apply the formula to the remaining rows in your table.

Troubleshooting #NAME? Error: If Excel returns a #NAME? error, it means your version of Excel does not support XLOOKUP or your system requires different regional separators (such as semicolons instead of commas).

Solution 2

Use INDEX and MATCH for Older Excel Versions

If you are using an older version of Excel that lacks the XLOOKUP function, you can achieve the same result using a combination of INDEX and MATCH.

Advanced Spreadsheet Tool

Master Complex Lookups with WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced functions like XLOOKUP, INDEX, and MATCH. You can seamlessly calculate rates and quantities based on multiple criteria with perfect Microsoft Excel compatibility.

1. Open your data tables: Launch WPS Spreadsheet and open the file containing your names, rates, and quantities.
2. Select the calculation cell: Click on the destination cell where you want the final amount to be calculated.
3. Enter the formula: Type =XLOOKUP(F2&G2,$A$2:$A$4&$B$2:$B$4,$C$2:$C$4)*H2 into the formula bar and press Enter.
4. Fill the data: Drag the fill handle downwards to instantly apply the calculation to the rest of the individuals in your table.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsBuilt-in support for dynamic arrays and modern functions like XLOOKUPLightweight, fast, and free to use for daily calculationsIntuitive interface for managing complex datasets and cross-table references

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return an #N/A error instead of a value?

An #N/A error occurs when the exact combination of the first and last name cannot be found in your lookup ranges. Verify that there are no typographical errors or trailing spaces in the names across both tables.

How can I handle missing names without displaying an error?

You can use the built-in [if_not_found] argument in the XLOOKUP function. For example, using =XLOOKUP(F2&G2,$A$2:$A$4&$B$2:$B$4,$C$2:$C$4,0)*H2 will output 0 instead of an error if the name combination is missing.

Can I perform this calculation if the full name is in a single cell?

Yes. If the full name is in one cell (e.g., F2) but split into two columns (A and B) in the rate table, you can adjust the lookup array to include a space. Use =XLOOKUP(F2,$A$2:$A$4&" "&$B$2:$B$4,$C$2:$C$4)*H2 assuming the full name contains a standard space between the first and last name.

Excel & Data Problems