Function Problems

How to Return a Rate Based on Two Data Validation Selections in Excel

Huda QurayshiHuda Qurayshi Nov 11, 2026 870 views

Question details

The user needs to retrieve a specific numerical rate from a data table by matching two criteria chosen from data validation drop-down lists (e.g., personnel description and rate schedule).

How to Return a Rate Based on Two Data Validation Selections in Excel
Product
Excel
Device & OS
not provided
Scenario
Looking up a dynamic billing or payment rate from a two-dimensional grid based on user selections in multiple drop-down menus.
Observed behavior
Requires a reliable formula to intersect the row and column criteria from the drop-downs to return the correct intersecting value.
Before you start

Ensure your lookup table is properly formatted as a two-dimensional grid, and that the items in your data validation drop-down lists exactly match the row and column headers in your data table without any extra spaces.

Solution 1Recommended

Use a Nested XLOOKUP Formula

XLOOKUP is a robust, modern function that can be nested to perform a two-way lookup, finding values across both rows and columns simultaneously.

The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. By placing an XLOOKUP formula inside the return array of another XLOOKUP, you can intersect a specific row and column.

1
Select the target cell

Click on the cell where you want the final rate to be displayed.

2
Enter the nested formula

Type the formula: =IFNA(XLOOKUP(selected_personnel,personnel_range,XLOOKUP(selected_schedule,schedule_range,rate_range)),0)

3
Update the cell references

Replace 'selected_personnel' and 'selected_schedule' with the cells containing your drop-down menus. Replace the ranges with the actual row, column, and data grid coordinates.

4
Calculate the result

Press the Enter key. The formula will now display the exact rate from the intersection of your two selections.

Use a Nested XLOOKUP Formula
Handling Errors: Wrapping the formula in the IFNA function ensures that if a user hasn't made a selection yet, the cell will display a '0' instead of an unsightly #N/A error.
Advanced Formulas in WPS Spreadsheet

Lookup Complex Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, INDEX, and MATCH, making it incredibly easy to pull specific data based on multiple data validation criteria without missing a beat.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data validation drop-downs and lookup table.
  2. 2. Select the output cell: Click the cell where you want to display the retrieved rate.
  3. 3. Input the formula: Type either the nested XLOOKUP or INDEX/MATCH formula, pointing to your data ranges.
  4. 4. Get the result: Hit Enter. WPS Spreadsheet instantly processes the two criteria and returns the correct rate.
Full compatibility with Microsoft Excel formulas including XLOOKUP and INDEX/MATCHIntuitive formula building tools with clear syntax highlightingFree to download and use for all your daily data analysis tasksLightweight application that opens large datasets and complex lookup tables quickly
microsoft office alternative - wps office

Frequently Asked Questions

Why is my lookup formula returning a #N/A error?

A #N/A error usually means the value selected in your data validation drop-down does not exactly match the text in your lookup range. Check both your drop-down list items and your table headers for invisible trailing spaces or spelling differences.

Can I use VLOOKUP instead of XLOOKUP for this task?

VLOOKUP is primarily designed for searching a single column. While you can use VLOOKUP combined with the MATCH function to find the correct column index dynamically, using nested XLOOKUP or INDEX/MATCH is generally more robust and less prone to errors if columns are inserted or deleted.

How do I create the drop-down lists for the personnel and schedule?

Select the cell where you want the drop-down. Go to the Data tab on the ribbon, click 'Data Validation', choose 'List' from the Allow drop-down menu, and highlight the range of cells containing your personnel or schedule headers as the Source.

What does the 0 mean in the MATCH function?

The 0 at the end of the MATCH function (e.g., MATCH(lookup_value, lookup_array, 0)) tells the formula to find an exact match only. If it cannot find an exact match, it will trigger an error rather than returning the closest approximate match.