Formula Errors

How to Match Two Columns and Return a Quantity in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a formula to compare two specific criteria values (such as Model and Style) between two worksheets and return the corresponding matching quantity from a third column.

Product: Excel
Device & OS: not provided
Scenario: Pulling quantitative data from a master dataset into a summary sheet based on multiple specific matching conditions.
Observed behavior: The user requires a method to correctly retrieve and display the matched quantity from Column C based on the criteria in Columns A and B.

Before you start

Ensure that your source worksheet and destination worksheet are both open, and that the data ranges for your criteria columns exactly mirror the row numbers of your quantity column.

Solution 1Recommended

Use the SUMIFS Function to Match Multiple Columns

The SUMIFS function is the most efficient method for matching multiple criteria and returning a quantity, provided the target column contains numeric values.

SUMIFS is designed to sum values that meet multiple conditions. If your combinations of Model and Style are unique, it will act as a lookup tool and return the exact single quantity.

Select the target cell

Navigate to the cell in your destination worksheet (e.g., cell C2 on Sheet2) where you want the matched quantity to be displayed.

Enter the SUMIFS formula

Input the formula: =SUMIFS(Sheet1!$C$2:$C$14,Sheet1!$A$2:$A$14,A2,Sheet1!$B$2:$B$14,B2) into the formula bar.

Apply the formula

Press Enter to execute the formula. Click the cell again, grab the small square fill handle in the bottom-right corner, and drag it down to fill the remaining rows.

Using Absolute References: The dollar signs ($) in the formula lock the data ranges in Sheet1, ensuring they do not shift when you copy the formula down your column.

Solution 2

Use INDEX and MATCH with Multiple Criteria

Combining INDEX and MATCH is a robust alternative, especially useful if the column you are returning contains text or if you want to find the first exact match without summing.

Advanced Data Matching

Match Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports complex formulas like SUMIFS, INDEX, and MATCH natively. You can effortlessly compare datasets, look up values across multiple sheets, and manage large quantities of data.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your two sheets.
2. Access the Formula tab: Select the target cell, navigate to the Formulas tab on the top ribbon, and click Insert Function.
3. Select SUMIFS or INDEX: Search for SUMIFS in the dialog box, select it, and use the visual helper to assign your criteria ranges and conditions easily.
4. Apply and fill: Click OK to apply the function, then double-click the cell's fill handle to apply the logic down your entire dataset.

Fully compatible with Microsoft Excel formulas and functions.Built-in formula suggestions and error-checking tools.Lightweight, fast, and completely free for complex data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return 0 even when there is a match?

This usually occurs if the data types between your criteria do not match (e.g., numbers stored as text) or if there are trailing spaces in the cells. Use the TRIM function to clean your text data or ensure all formatted values are consistent.

Can I use VLOOKUP to match two columns instead?

VLOOKUP can only search for a single value in the first column of an array. To use VLOOKUP for matching two columns, you must create a 'helper column' in your dataset that combines the two criteria using the '&' symbol, and then reference that helper column in your VLOOKUP formula.

Are INDEX and MATCH array formulas fully compatible with WPS Spreadsheet?

Yes, WPS Spreadsheet supports all standard lookup and reference formulas, including array operations with INDEX and MATCH. You can confidently use the exact same formula structures as you would in Microsoft Excel.

How can I match more than two columns?

Using the INDEX and MATCH method, you can append as many criteria as you need by continuing to use the ampersand (&). For example: =INDEX(ReturnRange, MATCH(Crit1&Crit2&Crit3, Range1&Range2&Range3, 0)).

Excel & Data Problems