Excel VLOOKUP Examples With a Drop-Down List

To make a drop-down selection drive a lookup in Excel for Microsoft 365 on Windows, create a list with Data > Data Validation, choose List, and set the Source to the range containing your choices. Then use an exact-match formula such as =VLOOKUP(F2,$A$2:$C$5,2,FALSE) to return a matching value. The example below shows how to build the list, connect it to VLOOKUP, and handle common issues.

How a drop-down list and VLOOKUP work together

Data Validation controls what a user can enter in a cell; VLOOKUP searches a table for a value and returns information from another column in the same row. Together, they let someone choose an item, code, or name from a controlled list and display related information without typing the lookup formula each time. The drop-down does not perform the lookup itself: it supplies the search value to the formula.

For a simple product example, put these headings and records in cells A1:C5:

A1: Product code; B1: Product; C1: Price
A2:C2: P-104, Notebook, 3.50
A3:C3: P-208, Pen, 1.25
A4:C4: P-315, Folder, 2.00
A5:C5: P-422, Marker, 1.75

Cell F2 will hold the selection. Cell G2 can show the product name, and H2 can show its price. This arrangement keeps the source table separate from the small lookup area, although the cells can be placed elsewhere in the workbook.

Create the drop-down from a range

For this basic example, the source codes and the drop-down are on the same worksheet. Select F2, then choose Data > Data Validation. On the Settings tab, set Allow to List, make sure In-cell dropdown is selected, and enter =$A$2:$A$5 in the Source box. Choose OK. F2 now has a selection arrow when the cell is active; select a code from it rather than typing one.

To apply the same rule to more rows, select a range such as F2:F50 before opening Data Validation. The source remains fixed at A2:A5, while each row’s lookup formula can refer to its own selection cell. If the list is on another worksheet, Excel’s Data Validation Source box does not accept a direct reference to a range on that other sheet in the usual way. Define a name for the source range with Formulas > Name Manager, then use that name as the validation source, for example =ProductCodes.

A named range makes the cross-sheet setup easier to read and maintain. In Name Manager, create the name ProductCodes and set Refers to to the code range on the source sheet. If the list will grow, consider storing it in an Excel Table and defining a name that refers to the table’s code column. Check the validation rule when adding new records so the choices continue to include them.

Return a related value with exact-match VLOOKUP

VLOOKUP searches the first column of its table range and returns a value from a column to the right. Its fourth argument should usually be FALSE for a drop-down lookup. FALSE requests an exact match; leaving this argument out or using TRUE can produce an approximate-match result, which is inappropriate for most product codes and names.

With product codes in A2:A5, product names in B2:B5, prices in C2:C5, and the selected code in F2, enter this formula in G2: =VLOOKUP(F2,$A$2:$C$5,2,FALSE). If F2 contains P-315, the formula returns Folder. The 2 means return a value from the second column of the selected table range, which is column B. The dollar signs keep the table range fixed if you copy the formula down.

To return the price instead, use this formula in H2: =VLOOKUP(F2,$A$2:$C$5,3,FALSE). With P-315 selected, it returns 2.00, because column C is the third column in A:C. The column number is counted from the start of the table range, not from the worksheet’s column letters. If you change the range to B2:C5, for instance, the price is in column 2 of that range.

For a two-column table containing only codes and names in A2:B5, this formula in G2 also returns Folder for P-315: =VLOOKUP(F2,$A$2:$B$5,2,FALSE). The lookup code must be in the table range’s leftmost column. VLOOKUP cannot search a column and return a result from a column to its left; if the source layout requires that, rearrange the table or use another lookup method.

Make the formula easier to use and copy

When F2 is empty, a plain VLOOKUP may display #N/A. You can keep the result cell visually blank until a selection is made. With the same product table in A2:C5 and the selection in F2, enter this in G2: =IF(F2="","",VLOOKUP(F2,$A$2:$C$5,2,FALSE)). It returns a blank while F2 is blank, and returns Folder when F2 contains P-315. The formula checks for an empty selection before attempting the lookup.

For a selection that may not exist in the source table, IFERROR can replace a lookup error with a helpful message. With the same layout, enter this in G2: =IFERROR(VLOOKUP(F2,$A$2:$C$5,2,FALSE),"Code not found"). If F2 contains P-315, the result is Folder; if it contains a code not found in A2:A5, the result is Code not found. IFERROR also catches errors other than #N/A, so use it thoughtfully: a broad replacement can hide an unrelated problem in the formula or source data.

Copy a formula down when each row has its own selection cell. For example, copying a formula from G2 to G3 changes the relative reference F2 to F3 while the dollar-signed source range stays at A2:C5. If the formula will be copied across columns as well, consider locking the selection column or row as appropriate. Confirm the result in a second row before filling a large range.

Use longer or changing choice lists

A fixed source such as =$A$2:$A$5 is straightforward, but it will not automatically include a new code entered in A6. One practical option is to leave room in the source range, such as =$A$2:$A$100, and keep the unused cells blank. Another is to use a named range that expands with an Excel Table. Tables are useful because they grow when records are added, and they make the source data easier to manage.

For lists stored on another sheet, a workbook-level name is often the clearest way to connect the range to Data Validation. Create a name such as ProductCodes in Name Manager and use =ProductCodes in the Source field. If the name refers to a Table column, test the drop-down after adding a row; the choice list and lookup table should both include the new record. A drop-down list can contain duplicate choices if the source range does, so remove duplicates in the source if each option should appear only once.

Keep the lookup key and its source values consistent. A code stored as text may not match a value stored as a number, even if they look similar on screen. Codes with leading zeros, such as 0042, are often best stored as text so Excel does not treat them as the number 42. VLOOKUP is not case-sensitive, so it treats ABC and abc as the same lookup text; use a different method if letter case must distinguish records.

VLOOKUP compared with XLOOKUP

VLOOKUP remains useful when the lookup value is in the leftmost column of a straightforward table, and it works in older Excel releases as well as Microsoft 365. Its column number can become fragile if someone inserts or rearranges columns, and it cannot return a result from a column to the left of the lookup column.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It lets you specify the lookup range and return range separately, which can make a formula easier to maintain when columns move. For the same data, with codes in A2:A5, product names in B2:B5, and the selection in F2, this formula in G2 returns Folder for P-315: =XLOOKUP(F2,A2:A5,B2:B5,"Code not found"). If you need the requested VLOOKUP approach for compatibility, use the exact-match examples above; the drop-down setup itself is independent of which lookup function you choose.

Common problems and how to fix them

#N/A: The selected value may not exactly match the first column of the lookup range. Check spelling, extra spaces, text-versus-number differences, and whether the selected code exists in the table. Confirm that the VLOOKUP range begins with the lookup column.
Wrong field returned: Recount the return-column number from the left edge of the table range. If the range starts in A, column C is number 3; it is not necessarily the third column on the worksheet if the range starts elsewhere.
Wrong result despite a match: Check that the fourth argument is FALSE. Approximate matching is not a safe default for unsorted product codes or names.
New items missing from the drop-down: The validation source may be a fixed range that does not reach the new row. Expand the range or update the named range, then check the lookup table too.
Drop-down arrow is not visible: Select the validated cell. Excel normally shows the in-cell arrow when the cell is active; also reopen Data Validation to confirm Allow is List and In-cell dropdown is selected.

Excel’s Windows shortcut Alt+Down Arrow opens a cell’s data-validation list when the cell is selected. On Mac, Option+Down Arrow opens a validation list in Excel for Mac. The ribbon path for this setup is Data > Data Validation in Microsoft 365 for Windows; menu wording and window layout can vary somewhat by Mac release, but the validation feature is available there as well.

Frequently Asked Questions

Can a VLOOKUP formula use the value selected in a drop-down?
Yes. Put the drop-down in one cell and use that cell reference as VLOOKUP’s first argument. For example, if the selected code is in F2, the formula searches for F2’s value in the first column of its table range.

Why does my VLOOKUP return #N/A when the value is in the list?
The values may not be identical even if they look alike. Check for extra spaces, different data types, leading zeros, or a lookup range that starts in the wrong column. Use FALSE as the final VLOOKUP argument to request an exact match.

Can the drop-down list be on another worksheet?
Yes. Define a name for the source range using Formulas > Name Manager, then enter that name in the Data Validation Source box. This avoids putting a direct cross-sheet reference in the Source field.

How do I return a price instead of a name?
Keep the lookup table range the same and change VLOOKUP’s column number to the position of the price column within that range. In the A:C example, the price is in position 3, so use 3 as the third argument.

Does VLOOKUP distinguish uppercase and lowercase codes?
No. VLOOKUP is case-insensitive, so codes that differ only by letter case are treated as matches. If case-sensitive matching is a requirement, use a different formula design rather than relying on VLOOKUP.

Keep the workbook maintainable

A dependable lookup sheet has one clear source table, a validation list that covers the available choices, and an exact-match formula whose range and return-column number are easy to verify. Add a new record as a test whenever you change the source setup, and check that both the drop-down and returned fields update as intended. This small check helps prevent stale choice lists and confusing lookup errors as the workbook grows.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.