How to Return a Rate Based on Two Data Validation Selections in Excel
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).

- 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.
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.
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.
Click on the cell where you want the final rate to be displayed.
Type the formula: =IFNA(XLOOKUP(selected_personnel,personnel_range,XLOOKUP(selected_schedule,schedule_range,rate_range)),0)
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.
Press the Enter key. The formula will now display the exact rate from the intersection of your two selections.

Use INDEX and MATCH Functions
For older spreadsheet versions that do not support XLOOKUP, combining INDEX and MATCH provides a highly reliable way to perform two-way lookups.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing your data validation drop-downs and lookup table.
- 2. Select the output cell: Click the cell where you want to display the retrieved rate.
- 3. Input the formula: Type either the nested XLOOKUP or INDEX/MATCH formula, pointing to your data ranges.
- 4. Get the result: Hit Enter. WPS Spreadsheet instantly processes the two criteria and returns the correct rate.

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.




