Formula Errors

How to Return Values for Matching Pairs in Two Columns in Excel

Natalie Taylor Oct 9, 2026 869 views

Question details

The user needs an Excel formula to look up and return specific categorical results based on multiple unique combinations of values present in two separate columns.

How to Return Values for Matching Pairs in Two Columns in Excel

Product: Excel
Device & OS: not provided
Scenario: Looking to evaluate combinations in columns A and B against a reference table of over 85 unique pairs to return specific categorized results (like Good, Bad, or Indifferent) in subsequent columns.
Observed behavior: A need for an efficient multi-condition lookup method to prevent excessively complex nested IF functions and resolve #VALUE! errors when matching many unique pairs.

Before you start

Before attempting multi-column match formulas, review how many unique combinations you have. For large datasets with many variables, ensure you set up a clean, dedicated reference table on a separate worksheet to prevent your primary formulas from becoming unmanageable.

Solution 1Recommended

Use INDEX and MATCH Array Formula for Large Datasets

This is the most scalable method when you have many combinations, utilizing Boolean logic to check two conditions simultaneously against a reference lookup table.

Instead of writing a massive nested IF formula for 85 combinations, it is vastly more efficient to maintain a separate Data sheet containing your criteria pairs and their corresponding results.

Create a Reference Lookup Table

On a new sheet named 'Data', place your first condition combinations in Column A, second condition combinations in Column B, the primary result in Column C, and the secondary result in Column D.

Input the Primary INDEX MATCH Formula

In your main sheet, select the cell where you want the first result to appear. Enter the formula: =INDEX(Data!$C$2:$C$100, MATCH(1, (A2=Data!$A$2:$A$100)*(B2=Data!$B$2:$B$100), 0))

Input the Secondary INDEX MATCH Formula

To get the secondary result, reference Column D in the INDEX array: =INDEX(Data!$D$2:$D$100, MATCH(1, (A2=Data!$A$2:$A$100)*(B2=Data!$B$2:$B$100), 0))

Execute as an Array Formula

If you are using an older version of Excel that does not support dynamic arrays natively, you must press Ctrl+Shift+Enter instead of just Enter to apply the formula correctly and avoid #VALUE! errors.

Use INDEX and MATCH Array Formula for Large Datasets

Organized Data Management: By placing your lookup table on a separate sheet, you can easily add, remove, or modify criteria pairs later without ever needing to edit the formulas on your main dashboard.

Solution 2

Use XLOOKUP for Modern Simplicity

If you are using a modern spreadsheet software version, XLOOKUP provides a cleaner and faster syntax to accomplish multi-column lookups by concatenating search values.

Solution 3

Use IFS and AND for Simple, Limited Conditions

Best suited for straightforward datasets where you only need to match a small handful of possible combinations without creating a separate reference table.

Advanced Data Lookups

Easily Handle Multi-Condition Lookups with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for modern array formulas, XLOOKUP, and classic INDEX/MATCH. It provides a lightweight, highly compatible environment to handle massive datasets and intricate multi-column matching operations efficiently.

1. Open Your Data: Launch WPS Spreadsheet and open your existing workbook containing the dual-column matching scenario.
2. Set Up a Reference Sheet: Organize your multiple matching pairs into a dedicated reference sheet to keep your primary workspace clean.
3. Insert the Lookup Formula: Use the formula bar to input your XLOOKUP or INDEX/MATCH formula. WPS Spreadsheet will instantly highlight referenced arrays.
4. Execute and Drag: Press Enter to obtain the matching result, then use the fill handle to copy the logic down to all your data rows seamlessly.

Fully compatible with Microsoft Excel formulas, .xlsx format, and syntax conventions.Built-in support for dynamic array formulas and XLOOKUP to easily match two or more columns.Completely free to use with a lightweight installation, ensuring fast operation even with thousands of lookup rows.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #VALUE! error with my INDEX MATCH formula?

In older versions of Excel, using multiplication syntax like (A=Range)*(B=Range) creates an array operation. You must confirm the formula by pressing Ctrl+Shift+Enter instead of just Enter. If you just press Enter, Excel may not know how to evaluate the array, resulting in a #VALUE! error.

Can I use a standard VLOOKUP to match multiple columns?

Standard VLOOKUP only searches for a single value in the first column of a table array. To use VLOOKUP for two columns, you must create a 'Helper Column' that combines the two values (e.g., =A2&B2) on your data sheet, and then use that helper column as your primary search column.

How does XLOOKUP make matching two columns easier?

XLOOKUP allows you to concatenate multiple criteria directly in the formula using the '&' symbol (e.g., A2&B2) and simultaneously concatenate the lookup arrays without needing helper columns or complex array keypress combinations like Ctrl+Shift+Enter.

Excel & Data Problems