Function Problems

How to Use Excel IF and VLOOKUP Formulas to Calculate Rates

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs an Excel formula to multiply a value in one column by a specific rate that depends on a text code in another column, displaying the result in a third column.

Product: Spreadsheets
Device & OS: not provided
Scenario: Calculating variable rates automatically based on specific category codes within a spreadsheet data set.
Observed behavior: The user wants a dynamic formula that correctly applies different multiplication rates based on identifier codes like 'C' or 'M' without syntax errors.

Before you start

Identify all the unique codes in your reference column and determine their corresponding rates before building your formula. Decide whether an IF function (best for 2 codes) or a lookup table (best for 3 or more codes) suits your data.

Solution 1

Use the IF Function for Two Alternating Codes

Ideal when you only have two specific codes (such as 'C' or 'M') that dictate the calculation rate.

If your data strictly contains only two alternatives, a simple IF function is the fastest way to calculate the rates.

Select the result cell

Click on cell G1 (or your corresponding output cell) where the calculated result should be displayed.

Enter the IF formula

Type `=IF(A1="C",207.13*F1,358.75*F1)` into the formula bar and press Enter. This multiplies F1 by 207.13 if A1 is 'C', otherwise it multiplies by 358.75.

Apply to the rest of the column

Drag the fill handle in the bottom-right corner of the cell downwards to apply this calculation to the remaining rows.

Syntax Error Warning: Do not include the word 'then' in an Excel IF formula. The commas automatically act as the 'then' and 'else' separators.

Solution 2Recommended

Use VLOOKUP with a Reference Table for Multiple Codes

Best for scenarios where your column contains more than two unique codes, making nested IF formulas overly complex.

Solution 3

Combine VLOOKUP and IFERROR to Handle Missing Codes

Prevents '#N/A' errors from displaying when a matching code is not found or the reference cell is left blank.

Advanced Formulas in WPS

Calculate Conditional Rates Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for complex data functions, including IF, VLOOKUP, and IFERROR. You can effortlessly manage variable calculations and analyze your data with a professional, easy-to-use interface.

1. Open your workbook: Launch WPS Spreadsheet and open your existing data file.
2. Select the target cell: Click on the cell where you want the calculated rate to appear.
3. Enter the formula: Type your IF or VLOOKUP formula directly into the formula bar and press Enter.
4. Fill down the column: Drag the fill handle at the corner of the cell downwards to apply the formula to all relevant rows.

100% compatible with Microsoft Excel formulas and .xlsx formatsFamiliar interface makes writing and debugging complex formulas easyLightweight, fast, and free to use for everyday data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IF formula fail when I type the word 'then'?

Excel formula syntax relies on commas to separate logical arguments. The first comma after your logical test acts as the 'then' operator, so typing the actual word causes a syntax error.

What should I do if my column has more than two condition codes?

If you have three or more condition codes, using a separate lookup table with the VLOOKUP function is much more efficient and easier to update than writing deeply nested IF formulas.

How do I prevent '#N/A' errors when a code is missing?

Wrap your VLOOKUP formula inside an IFERROR function, like `=IFERROR(VLOOKUP(...), "")`. This tells the spreadsheet to display a blank cell instead of an error message when a match cannot be found.

Excel & Data Problems