Function Problems

How to Use Excel Formulas for Multiple Lookups and Cost-Share Calculations

Algirdas Jasaitis Oct 1, 2026 868 views

Question details

The user needs a formula to calculate an employee cost-sharing amount by matching a specific benefit category and plan group, retrieving a percentage from a table, and multiplying it by the medical premium.

How to Calculate Cost-Share Amounts with Multiple Lookups in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating employee medical premium cost-shares based on multiple criteria such as benefit type and plan group.
Observed behavior: Requires an Excel formula that evaluates a specific string condition, looks up a corresponding percentage based on a group ID, and multiplies the result by a base monetary value.

Before you start

Ensure your employee plan data, premium amounts, and a separate cost-share percentage reference table are clearly organized in your workbook before applying the formulas.

Solution 1Recommended

Use Combined IF and VLOOKUP Formulas

Combine the IF function to conditionally check the benefit category and the VLOOKUP function to retrieve the correct percentage, then multiply it by the premium.

This approach uses the IF function as the primary logical test. If the row belongs to the target benefit category, it triggers the VLOOKUP function to find the exact cost-share percentage based on the employee's plan type, and finally calculates the total share amount.

Identify your criteria cells

Determine the locations of your data. For example, let cell A2 contain the benefit type (e.g., 'Medical - Cost Share'), cell B2 contain the plan group, and cell C2 contain the total medical premium.

Locate your lookup reference table

Verify that your cost-share percentage table is set up properly, typically on another sheet like 'Sheet2'. Ensure the first column contains the plan group and the second column contains the percentage. The range might look like Sheet2!$C$2:$D$3.

Enter the formula

Select the cell where you want the final cost-share amount to appear and type the following formula: =IF(A2="Medical - Cost Share", C2*VLOOKUP(B2, Sheet2!$C$2:$D$3, 2, FALSE), 0).

Apply to the remaining dataset

Press Enter to execute the calculation. Click on the cell, grab the small square fill handle in the bottom-right corner, and drag it down to apply the formula to the rest of the rows in your table.

Use Absolute References: Always use absolute references (the dollar signs, like $C$2:$D$3) for your lookup table range so it does not shift when copying the formula down the column.

Process Data Efficiently with WPS Spreadsheet

Calculate Complex Formulas Easily in WPS Office

WPS Spreadsheet fully supports advanced logical and lookup functions like IF, VLOOKUP, and XLOOKUP, making it simple to process premium cost-shares, large HR datasets, and multiple-criteria lookups.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open the file containing your employee premium data and plan group reference tables.
2. Use the Insert Function tool: Click the 'Formulas' tab on the ribbon and select 'Insert Function' to easily search for and configure the IF and VLOOKUP functions.
3. Input the calculation: Enter your combined lookup formula directly into the formula bar and press Enter to instantly generate the accurate cost-share calculation.
4. Drag to fill: Use the quick-fill handle at the bottom right corner of the selected cell to instantly apply the formula down your entire employee list.

100% compatible with Microsoft Excel formulas, functions, and file formatsBuilt-in formula auditing tools to quickly trace dependencies and identify #N/A errorsLightweight application with fast processing speeds for large and complex datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

An #N/A error usually occurs if the plan group in your main data does not exactly match the lookup table. Check for trailing spaces, typos, or formatting differences between the text in cell B2 and the text in the first column of your reference table.

How do I look up values with multiple conditions without nesting IF statements?

You can use the INDEX and MATCH functions combined, or utilize the XLOOKUP function. XLOOKUP evaluates multiple criteria directly and returns corresponding values without requiring complex nested statements.

Can I use XLOOKUP for this cost-share calculation instead?

Yes. XLOOKUP is generally more flexible than VLOOKUP. If using XLOOKUP, your formula would look similar to: =IF(A2="Medical - Cost Share", C2*XLOOKUP(B2, Sheet2!$C$2:$C$3, Sheet2!$D$2:$D$3), 0).

Excel & Data Problems