How to Use VLOOKUP on a Worksheet Selected from a Drop-Down List in WPS Office Spreadsheets
Question details
The user needs to run a VLOOKUP formula that switches its lookup sheet automatically based on a worksheet name chosen from a drop-down list.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A cell contains a drop-down list of worksheet names, and the user wants the VLOOKUP result to come from whichever sheet is selected.
- Observed behavior
- A standard VLOOKUP uses a fixed worksheet reference and does not automatically change when the drop-down selection changes.
Make sure the drop-down cell contains the exact worksheet names, including any spaces or special characters, and verify that each target worksheet uses the same lookup range.
Use INDIRECT with VLOOKUP to reference the selected worksheet name
Use this when the worksheet name is stored in a cell such as B2 and you need a dynamic reference without rewriting the formula.
The INDIRECT function converts text into a cell reference. When you concatenate the selected sheet name with a fixed range, VLOOKUP reads the reference as the chosen worksheet instead of a static sheet.
In WPS Spreadsheets, select the cell that will hold the sheet name, go to Data > Data Validation, choose List, and enter or select the exact worksheet names.
Click the result cell, enter =VLOOKUP(A2,INDIRECT("'"&B2&"'!A2:D100"),4,FALSE), and press Enter. Adjust A2 to the lookup value, B2 to the drop-down cell, A2:D100 to your common data range, and 4 to the column number to return.
Select another sheet name from the drop-down list and confirm that the result updates from the corresponding worksheet.
Use VLOOKUP with INDIRECT for Drop-Down Sheet Selection in WPS Office
WPS Office Spreadsheets supports Excel-compatible VLOOKUP and INDIRECT functions, so you can build a dynamic workbook that reads from the worksheet selected in a drop-down list without changing the formula.
- 1. Download and install WPS Office: Go to the official WPS Office website, download WPS Office Free, and install WPS Spreadsheets on your computer.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Spreadsheets, open the .xlsx workbook, and go to the sheet where the drop-down list is located.
- 3. Set up the drop-down list: Select the cell for the worksheet name, click Data > Data Validation, choose List, and add the exact worksheet names.
- 4. Apply the INDIRECT VLOOKUP formula: In the result cell enter =VLOOKUP(A2,INDIRECT("'"&B2&"'!A2:D100"),4,FALSE), adjusting the cells and range to your data.
- 5. Test the dynamic lookup: Choose different names from the drop-down list and verify that the result changes to the matching value from the selected sheet.
Frequently Asked Questions
Why does VLOOKUP return #REF! when I change the drop-down list?
Usually the sheet name in the cell does not exactly match the worksheet name, or the referenced range does not exist on the selected sheet. Confirm that the name in B2 matches exactly and that every listed sheet contains the range A2:D100.
Do I need to put quotes around the sheet name in INDIRECT?
Yes. Include single quotes inside double quotes when the sheet name contains spaces or special characters, such as INDIRECT("'"&B2&"'!A2:D100"). If sheet names are simple one-word names without spaces, you can use INDIRECT(B2&"!A2:D100").
Can I use this method with a drop-down list that references another workbook?
Yes, INDIRECT can build a reference to another workbook, but in WPS Spreadsheets the external workbook must generally be open for INDIRECT to return values.
Does WPS Office support the same INDIRECT and VLOOKUP syntax as Excel 365?
Yes. WPS Spreadsheets supports Excel-compatible functions, so the same formula works when you open .xlsx files in WPS Office.
How do I make the drop-down list show only existing worksheet names?
Create a helper range that lists the exact worksheet names, then select the drop-down cell, go to Data > Data Validation > List, and use that helper range as the source.
