Return a Value Based on Two Drop-Down Selections in Excel
Question details
Return a value from a lookup table based on two drop-down selections.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user selects a number and a color from two drop-down lists and wants a result cell to find the corresponding table entry.
- Observed behavior
- The result cell should display the value associated with the selected number and color.
Arrange the lookup table with numbers in F2:F11, colors in G2:G11, and corresponding results in H2:H11. The examples assume the drop-down selections are in A2 and B2 and the result will appear in C2.
Use XLOOKUP to match both selections
Use this formula in Excel versions that support XLOOKUP, such as Microsoft 365 and Excel 2021.
Click C2, or another cell where you want the matching value to appear.
In the formula bar, enter =IF(OR(A2="",B2=""),"",XLOOKUP(1,($F$2:$F$11=A2)*($G$2:$G$11=B2),$H$2:$H$11,"")) and press Enter.
Choose a number in A2 and a color in B2. C2 displays the result from column H when both selections match a table row.

Use INDEX and MATCH with a helper column
Use a helper column when XLOOKUP is unavailable or you want a formula that also works in WPS Spreadsheet.
Match Two Drop-Down Selections in WPS Spreadsheet
Use a helper column with INDEX and MATCH in WPS Spreadsheet to return a value for a selected number and color.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook. Place the selections in A2 and B2 and the lookup data in F2:H11.
- 2. Build helper keys: Enter =F2&"|"&G2 in I2 and drag the fill handle through I11. Make sure the separator does not occur in the lookup values.
- 3. Add the lookup formula: In C2, enter =IF(OR(A2="",B2=""),"",IFERROR(INDEX($H$2:$H$11,MATCH(A2&"|"&B2,$I$2:$I$11,0)),"")) and press Enter.
- 4. Test and save: Choose values from the drop-downs in A2 and B2, check the result in C2, and use File > Save As to save the workbook as an XLSX file.

Frequently Asked Questions
What happens if the two selections do not match a table row?
Both formulas shown return a blank. In the XLOOKUP formula, replace the empty not-found argument with "Not found" if you prefer a message.
What if more than one row has the same number and color?
XLOOKUP and MATCH return the first matching row. Remove duplicate pairs from the lookup table if each pair should have only one result.
Can I use these formulas if my table is in different columns?
Yes. Replace the references to F2:F11, G2:G11, and H2:H11 with your criteria and result ranges. Keep the ranges the same size and aligned by row.
Why is the result blank when the values look correct?
Check that both drop-down cells are filled and that the number and color match the table values. Look for extra spaces or numbers stored as text, and confirm that the formula ranges include the matching row.




