Fix Excel VLOOKUP Errors with Table and Drop-Down References
Question details
The user needs to use a drop-down selection as the lookup value in an Excel VLOOKUP formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses a drop-down list to select an item and a table to return a related value.
- Observed behavior
- A current-row structured reference such as [@Item] fails when the formula is outside the appropriate table row.
Identify the cell that displays the selected drop-down item and the table or range containing the lookup data. Make sure the lookup values are in the first column of that range.
Reference the selected drop-down cell in VLOOKUP
Use this approach when the formula is outside the table or refers to the drop-down list source instead of the selected value.
A reference such as [@Item] refers to the current row of a table; it is not a general reference to a drop-down selection. The drop-down's source list is also different from the cell that displays the selected item.
On the Form sheet, select the cell that displays the chosen item. For example, the selection may be in C2.
Select the lookup table or range and confirm that its first column contains the items and its second column contains the values to return. In this example, the table or named range is called Data.
In the result cell on the Form sheet, enter =VLOOKUP(C2,Data,2,FALSE). If the formula is on another sheet, use =VLOOKUP(Form!C2,Data,2,FALSE) instead.
Choose an item from the drop-down in C2 and check the result cell. If you get #N/A, verify that the selected item matches a value in the first column of Data.

Use a Drop-Down Selection in VLOOKUP with WPS Spreadsheet
Open your workbook in WPS Spreadsheet and create an exact-match VLOOKUP that refers to the cell containing the selected drop-down value.
- 1. Open the workbook: Open WPS Spreadsheet and select Open to choose your Excel workbook.
- 2. Identify the cells: Click the drop-down cell and note its address, then select the lookup data range. For example, the selection might be in C2 and the data on the Data sheet in A2:B100.
- 3. Enter VLOOKUP: In a result cell on the same sheet as C2, enter =VLOOKUP(C2,Data!$A$2:$B$100,2,FALSE), adjusting the references to match your workbook.
- 4. Check the result: Select an item from the drop-down and confirm that the formula returns the corresponding value from the second column of the lookup range.

Frequently Asked Questions
Why does [@Item] fail outside my Excel table?
The [@Item] reference means the Item value in the current table row. Outside the appropriate table-row context, use a cell reference such as C2 for the selected drop-down value.
Should VLOOKUP refer to the drop-down list's named range?
The first VLOOKUP argument should refer to the cell displaying the selected item, such as C2. Use the lookup table or range as the second argument, even if the drop-down source has a different name.
Why does my formula return #N/A?
With FALSE as the fourth argument, VLOOKUP looks for an exact match in the first column of the lookup range. Check that the selected item appears there and that extra spaces or differences between text and numbers are not preventing a match.
How do I reference the drop-down cell from another sheet?
Add the sheet name before the cell address. For a selection in C2 on the Form sheet, use Form!C2 as the first argument, as in =VLOOKUP(Form!C2,Data,2,FALSE).




