Function Problems

How to Return a Rate from Two Dropdown Lists Using Excel Formulas

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to construct a formula that looks up and returns a specific rate from a data table based on the selections made in two different dropdown lists.

Product: Excel
Device & OS: not provided
Scenario: Looking up dynamic pricing or rates in a matrix table where the row represents one variable (Billing Type) and the column represents another variable (Rate Type).
Observed behavior: Requires a functional formula to accurately identify and extract the intersecting value based on two distinct user criteria.

Before you start

Ensure your data is organized into a matrix format with your first criteria (e.g., Billing Type) listed in a single column and your second criteria (e.g., Rate Type) listed in a single row across the top.

Solution 1Recommended

Use INDEX and MATCH for a Two-Way Lookup

Combining the INDEX function with two MATCH functions allows you to dynamically look up data at the intersection of a specific row and column.

The INDEX function returns the value of a cell within a designated range based on row and column numbers. By nesting two MATCH functions inside the INDEX function, you can automatically calculate those row and column numbers based on the user's dropdown selections.

Start the INDEX function

Select the cell where you want the final rate to appear and type =INDEX(. Then, select your entire grid of rates (for example, $H$4:$K$7). Do not include the row headers or column headers in this range. Use absolute references (F4) so the range remains fixed.

Add the first MATCH function for the row

Type a comma, then add MATCH(B4, $G$4:$G$7, 0). In this example, B4 is the cell containing your first dropdown selection (Billing Type), $G$4:$G$7 is the column containing the row headers, and 0 tells Excel to look for an exact match.

Add the second MATCH function for the column

Type another comma, then add MATCH(C4, $H$3:$K$3, 0). Here, C4 is the cell with your second dropdown selection (Rate Type), $H$3:$K$3 is the row containing the column headers, and 0 dictates an exact match.

Close the formula and execute

Close the brackets to complete the formula: =INDEX($H$4:$K$7, MATCH(B4, $G$4:$G$7, 0), MATCH(C4, $H$3:$K$3, 0)). Press Enter. The formula will now dynamically return the correct rate when different dropdown values are chosen.

Locking References: Using dollar signs ($) in your ranges creates absolute references. This ensures your lookup table boundaries do not shift if you drag or copy the formula to other cells.

Try WPS Spreadsheet

Effortlessly Manage Data and Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data lookups, including INDEX, MATCH, and XLOOKUP. It is a powerful, lightweight, and free alternative to Excel for all your data analysis and calculation needs.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the pricing or rate tables.
2. Set up Dropdown Lists: Select the target cell, navigate to the Data tab, and click Data Validation to easily define your dropdown list source ranges.
3. Apply the lookup formula: Enter the INDEX and MATCH formula directly into the formula bar. WPS Spreadsheet will calculate and return the correct matrix value instantly.

100% compatible with Microsoft Excel (.xlsx) formats and standard formulasFree and lightweight suite available for PC, Mac, and mobile devicesBuilt-in data validation tools to easily create and manage dropdown listsClean, familiar user interface for seamless workflow migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return an #N/A error?

An #N/A error typically occurs when the MATCH function cannot find the exact value from the dropdown list in the specified header range. Verify that there are no trailing spaces or typos in your dropdown source data or your table headers.

Can I use XLOOKUP instead of INDEX and MATCH?

Yes. If you are using a modern version of Excel or WPS Spreadsheet, you can nest XLOOKUP functions for a two-way lookup. For example: =XLOOKUP(B4, G4:G7, XLOOKUP(C4, H3:K3, H4:K7)).

How do I create the dropdown lists for this formula?

To create a dropdown list, select the cell where you want the dropdown, go to the Data tab, and select Data Validation. Under the 'Allow' drop-down, choose 'List', and then highlight the cells containing your criteria (like Billing Types) in the 'Source' box.

Excel & Data Problems