Function Problems

How to Use Two Excel Drop-Down Lists for a Matrix Lookup

Camila Milosovich Oct 10, 2026 871 views

Question details

Retrieve a specific value from a data matrix based on the selection from two separate drop-down lists.

How to Use Two Excel Drop-Down Lists for a Lookup

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic pricing matrix or schedule where a user selects a starting point and destination (e.g., pickup and drop-off locations) to automatically fetch the corresponding price or distance.
Observed behavior: The user needs to combine Data Validation for list selection with a combination of INDEX and MATCH functions to execute a two-way lookup against row and column headers.

Before you start

Ensure your data matrix is properly organized with clear, unique headers across the top row (e.g., drop-off locations) and down the first column (e.g., pickup locations) before creating your formulas.

Solution 1Recommended

Use INDEX and MATCH with Data Validation

Create user-friendly drop-down menus using Data Validation, then use a nested INDEX and MATCH formula to find the exact intersection of your two selections.

A two-way lookup requires identifying both the row number and the column number to find an intersection in a table. By using two MATCH functions inside an INDEX function, Excel dynamically locates the exact data point based on your drop-down list selections.

Create Drop-Down Lists

Select the cell for your first input (e.g., PickupCell). Go to the 'Data' tab on the ribbon and click 'Data Validation'. Under the 'Allow' drop-down, select 'List'. In the 'Source' box, highlight your row headers (e.g., $A$2:$A$5). Repeat this process in a different cell for the column headers (e.g., DropoffCell) using $B$1:$E$1.

Enter the INDEX MATCH Formula

Select the cell where you want the result to appear. Enter the formula: =INDEX($B$2:$E$5, MATCH(PickupCell, $A$2:$A$5, 0), MATCH(DropoffCell, $B$1:$E$1, 0)). Replace 'PickupCell' and 'DropoffCell' with the actual cell references of your drop-down lists.

Test the Lookup

Change the values in both of your newly created drop-down lists. The formula cell will instantly update to show the intersecting value from your pricing matrix.

Use INDEX and MATCH with Data Validation

Pro Tip: Always use absolute cell references (the $ symbol) for your matrix ranges (e.g., $B$2:$E$5). This ensures your lookup range remains locked if you copy the formula to another location.

Effortless Data Management

Create Dynamic Lookups Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like INDEX and MATCH, alongside intuitive Data Validation tools. Build professional, dynamic pricing matrices and dashboards completely free.

1. Open Your Matrix File: Launch WPS Spreadsheet and open your document containing the data table.
2. Insert Drop-Down Lists: Navigate to Data > Validation to quickly assign your row and column headers as drop-down lists.
3. Apply the Lookup Formula: Type your =INDEX(range, MATCH(), MATCH()) formula into the designated result cell.
4. Calculate Instantly: Press Enter to see your two-way lookup function perfectly execute.

100% compatibility with Microsoft Excel formulas, including INDEX, MATCH, and XLOOKUPIntuitive and easy-to-use Data Validation wizard for seamless drop-down menusLightweight application with rapid calculation speeds for large data matricesCompletely free to use with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

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

The #N/A error usually occurs when the selected value in your drop-down list doesn't exactly match the headers in your matrix. Check for hidden trailing spaces, typos, or mismatched formatting between the list source and the lookup criteria.

Can I use VLOOKUP instead of INDEX and MATCH for a two-way lookup?

While it is possible to combine VLOOKUP with MATCH for a two-way lookup, INDEX and MATCH is generally preferred. It is more flexible, processes faster on large datasets, and doesn't break if you insert or delete columns within your matrix.

How do I automatically update my drop-down lists if I add new locations to the matrix?

To make your lists dynamic, format your row and column headers as an Excel Table (Ctrl+T), or use named ranges with the OFFSET function. When you add new locations, your Data Validation drop-downs will expand automatically.

Excel & Data Problems