Function Problems

How to Return a Matching Excel Value Using Two Criteria

Huma Ashraf Ch Oct 10, 2026 868 views

Question details

The user wants to find and display a specific value from a third column based on matching criteria selected from two separate drop-down lists.

How to Return a Matching Excel Value Using Two Criteria

Product: Excel
Device & OS: not provided
Scenario: Creating an automated spreadsheet (like a risk assessment matrix) where selecting two specific parameters from drop-down menus automatically retrieves the corresponding result from a data table.
Observed behavior: The user needs a formula capable of performing a multi-criteria lookup, checking columns A and B simultaneously, and returning the exact matched value from column C into column D.

Before you start

Ensure your lookup data is organized in structured columns without merged cells, and that the text in your criteria drop-down lists exactly matches the text in your source data columns.

Solution 1Recommended

Use the XLOOKUP Function (Recommended for Newer Versions)

The XLOOKUP function provides a simple, modern way to handle multi-criteria lookups by concatenating the lookup values and lookup arrays.

If you are using a recent version of Excel or WPS Office, XLOOKUP is the most efficient and readable method for multi-criteria lookups. By using the ampersand (&) operator, you can combine multiple lookup values into a single search parameter.

Select the destination cell

Click on the cell (e.g., D2) where you want the matched value to automatically appear based on your drop-down selections.

Enter the XLOOKUP formula

Type the formula: =XLOOKUP(A2&B2, A:A&B:B, C:C) into the formula bar. In this example, A2 and B2 are the drop-down cells, A:A and B:B are the columns to search, and C:C contains the result to return.

Apply the formula

Press Enter. The function will seamlessly combine the criteria, find the exact match in the corresponding arrays, and return your desired value.

Use the XLOOKUP Function (Recommended for Newer Versions)

Tip for multiple criteria: You can string together more than two criteria simply by adding more ampersands, such as =XLOOKUP(A2&B2&C2, Array1&Array2&Array3, ResultArray).

Solution 2

Use INDEX and MATCH (For Older Versions)

A classic, universally supported approach that works in almost all versions of spreadsheet software by utilizing Boolean logic within an array formula.

Solution 3

Create a Helper Column (Simplest Approach)

This method avoids complex array formulas by combining the criteria into a single new column, making standard VLOOKUP or INDEX/MATCH possible.

Easily Handle Complex Formulas in WPS Office

Use WPS Spreadsheet for Seamless Multi-Criteria Lookups

WPS Spreadsheet fully supports advanced modern functions like XLOOKUP alongside classic formulas like INDEX and MATCH. You can easily build automated tracking systems, risk matrices, and dynamic drop-down lists with perfect Microsoft Excel compatibility.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your tables.
2. Set up drop-down lists: Go to the Data tab, click 'Validation', and choose 'List' to create your criteria selection cells.
3. Enter your formula: Select the result cell and simply type =XLOOKUP(Criteria1&Criteria2, Range1&Range2, ResultRange).
4. Get instant results: Press Enter. Your spreadsheet will now dynamically update the returned value whenever drop-down selections change.

Free, lightweight, and fast alternative to Microsoft ExcelFull support for advanced formulas including XLOOKUP, INDEX, and MATCHSeamless compatibility with standard .xlsx and .xls file formatsIntuitive built-in Data Validation tools for creating drop-down menus

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDEX MATCH formula returning a #VALUE! error?

This commonly happens in older spreadsheet versions if you press just 'Enter' instead of 'Ctrl + Shift + Enter'. Multi-criteria INDEX MATCH formulas using multiplication evaluate as arrays, meaning they require the Ctrl + Shift + Enter key combination to compute correctly.

How do I create drop-down lists for my criteria?

Select the cell where you want the drop-down to appear. Go to the 'Data' tab and click 'Data Validation'. In the settings, change the 'Allow' dropdown to 'List', and in the 'Source' box, select the range of cells that contain your dropdown options.

Can I use XLOOKUP with more than two criteria?

Yes. The syntax remains exactly the same. You just continue concatenating your lookup values and lookup arrays with the ampersand (&) operator. For example: =XLOOKUP(A1&B1&C1, RangeA&RangeB&RangeC, ResultRange).

What does the '1' mean in the MATCH array formula?

In the formula MATCH(1, (RangeA=CritA)*(RangeB=CritB), 0), the '1' represents TRUE. The logic (Range=Crit) evaluates to TRUE (1) or FALSE (0). Multiplying the conditions creates an array of 1s and 0s. The MATCH function looks for the number 1—the exact row where all conditions were simultaneously TRUE.

Excel & Data Problems