VLOOKUP Based on a Dropdown in Google Sheets: How It Works

Yes. In Google Sheets, create a dropdown in a cell such as E2, then use VLOOKUP to find the selected value in a table and return a related result. If product names are in A2:A100 and categories are in C2:C100, with the full lookup table in A2:D100, enter =VLOOKUP(E2, $A$2:$D$100, 3, FALSE) in the result cell. Choosing a product in E2 will show its category.

What a dropdown-driven VLOOKUP does

The dropdown and the lookup formula do different jobs. Data validation limits or guides what someone can enter in the selection cell; VLOOKUP uses that cell’s value as a search key. When the selection changes, Sheets recalculates the formula and displays the corresponding value from the table. No script or button is needed for this basic setup.

For a concrete example, imagine a sheet with these columns: product in column A, price in B, category in C, and stock count in D. A dropdown in E2 lets a user choose a product. A formula in F2 can display its category, while a formula in G2 can display its price. The table can be a small reference list or a larger inventory catalog.

The dropdown cell supplies the lookup value.
The first column of the VLOOKUP range contains the values to search.
The column index tells VLOOKUP which field to return.
FALSE requests an exact match, which is generally what a dropdown lookup needs.

Create the dropdown in Google Sheets

In the Sheets web browser interface, select E2, then choose Data > Data validation and click Add rule. Under Criteria, choose Dropdown to type the allowed choices directly, or choose Dropdown (from a range) to use values already listed in cells. The dropdown can be made from product names in the lookup table or from a separate list intended for selection.

For a range-based list, select the cells containing the choices, such as A2:A100, and save the rule. If you type the choices manually, enter each option as a separate item in the rule. A range-based list is usually easier to maintain when choices change: edit the source cells rather than revising every dropdown rule. You can also use Insert > Dropdown to start creating a dropdown, then configure its options in the data-validation panel.

After saving, click E2 and choose an item from its dropdown. A dropdown is not a lookup by itself: it only places a selected value in E2. The VLOOKUP formula in another cell is what retrieves the matching category, price, or other field. If you need the same behavior on many rows, create the validation rule for the intended input cells, not only E2.

Build the VLOOKUP formula

Assume row 1 contains headers, product names are in A2:A100, prices in B2:B100, categories in C2:C100, and stock counts in D2:D100. Put the dropdown selection in E2. To return the category in F2, use =VLOOKUP(E2, $A$2:$D$100, 3, FALSE). If the selected product is listed with category “Kitchen,” the result is Kitchen. The number 3 means the third column of the chosen range, A:D, which is column C on the sheet.

The arguments have a specific order: lookup value, table range, return-column index, and match mode. The dollar signs keep the table range fixed if you copy the formula elsewhere. The reference E2 is relative, so a copied formula in F3 will look at E3. To return price from the same table, use index 2; to return stock count, use index 4. The index is counted from the left edge of the selected range, not from the worksheet’s column A.

Use FALSE for exact matching. A dropdown choice should normally be identical to an entry in the key column, and an approximate match can return a different row than intended. Google Sheets can treat the final argument as optional, but leaving it out can invoke approximate-match behavior, so spelling out FALSE makes the purpose clear. In some spreadsheet locales, formula arguments are separated with semicolons instead of commas.

Keep the formula readable when nothing is selected

A blank dropdown or a value missing from the lookup table can produce an error rather than a useful display. With product choices in E2 and the lookup table in A2:D100, this version leaves the result empty until a choice is made and shows a message if that choice is not found: =IF(E2="", "", IFERROR(VLOOKUP(E2, $A$2:$D$100, 3, FALSE), "Not found")). It still returns the category for a valid selection.

IFERROR is convenient, but it can hide problems beyond a missing product, including a misspelled range or an incorrect column index. While building a sheet, consider using the plain VLOOKUP first so that errors remain visible. Add IFERROR once the formula is understood and decide what message is helpful to the people using the sheet. A label such as Not found is often more informative than a blank cell, especially in a report.

Make the lookup table easy to maintain

The fixed range A2:D100 is clear for a known-size list, but it will not include new rows added below row 100. If the catalog grows, expand the range in both the dropdown’s source and the formula. For a continuously growing table, Google Sheets accepts an open-ended range such as A2:D; using it avoids a routine boundary update, though the workbook may calculate more efficiently with a reasonably sized range in very large sheets.

Keep one unique key per product whenever possible. If the same product name appears more than once, VLOOKUP returns the first matching row in the range. That may be correct for repeated records, but it can produce surprising results if the rows represent different sizes, locations, or suppliers. In those cases, use a distinctive key such as a product ID, or combine identifying fields into a separate key column.

When copying a formula down, lock the lookup table with dollar signs and let the dropdown reference change by row. For example, a formula in F2 using E2 and $A$2:$D$100 can be filled down so row 3 reads its selection from E3. Check the first few rows after filling to confirm that the selection reference advances while the table range stays fixed.

Common problems and how to fix them

The result is an error: Check that the selected value exists in the first column of the formula’s range and that the range starts in the correct place. Confirm that the return index is no larger than the number of columns in the range.
The wrong field appears: Recount the return index from the first column of the selected range. For A:D, B is 2 and C is 3; if the range instead starts at B, the index positions change.
A choice is missing from the dropdown: Inspect the dropdown’s source range. It may stop before the newest entries, contain blanks, or point to a different list than the lookup table.
A choice appears but does not match: Check for extra spaces, inconsistent spelling, or mismatched identifiers. A dropdown sourced directly from the key column reduces typing differences, although it does not fix duplicate keys.
The formula shows the same result on every row: Verify that the dropdown reference is relative, such as E2, while the table range is locked, such as $A$2:$D$100. An accidental $ before the row number in E2 can keep every copied formula tied to one selection.

It is also worth distinguishing a valid zero or blank in the return column from a failed match. If the matching row has an empty category cell, the lookup may return a visually empty result even though it found the product. Check the source row before changing the formula; otherwise, an error-handling wrapper may conceal what is actually a data-quality issue.

VLOOKUP alternatives in Google Sheets

VLOOKUP is useful when the lookup key is in the leftmost column of a contiguous table and you want a field to its right. It is widely recognized and easy to explain, but its numbered return column can become fragile if columns are inserted or the range is rearranged. Keep the selected range and index aligned whenever the table structure changes.

XLOOKUP can name the lookup column and result column separately, so it can return a value from a column to the left or right. With product names in A2:A100 and categories in C2:C100, and the selection in E2, use =XLOOKUP(E2, A2:A100, C2:C100, "Not found"). Google Sheets supports XLOOKUP. INDEX with MATCH is another option when a formula needs separate lookup and result ranges, while FILTER can return multiple matching rows instead of just one. For a straightforward one-row result from a leftmost key column, VLOOKUP remains a reasonable choice.

Excel has its own VLOOKUP and data-validation features, but its menus and dropdown setup steps differ from Google Sheets. The directions here refer to Sheets in a web browser; they are not Excel ribbon instructions. If a workbook is moved between spreadsheet apps, review formulas and validation rules in the destination because support and interface details can differ.

Frequently Asked Questions

Can a Google Sheets dropdown automatically update a VLOOKUP?
Yes. The formula recalculates when the dropdown cell changes. The formula must refer to that cell, and the selected value must match an entry in the first column of its lookup range.

Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the first column of its range and returns a value from a column to its right. Use XLOOKUP or an INDEX-and-MATCH approach when the result is to the left of the key.

Why does my VLOOKUP return the wrong row?
Check that the last argument is FALSE and that the first column of the range contains the intended key. Also check for duplicate keys: VLOOKUP returns the first match, not every matching row.

Can I use a dropdown list from another sheet?
Yes. A range-based dropdown can use values stored elsewhere in the spreadsheet. Choose the source range in the data-validation rule, then make sure that the VLOOKUP table itself still includes the corresponding key values.

Can one dropdown fill several result cells?
Yes. Put separate lookup formulas in the result cells, using the same dropdown reference and changing the return-column index. For more complex reports, a single formula using a function such as XLOOKUP or FILTER may be easier to manage.

Practical setup checklist

Before sharing the sheet, make one selection and verify that the returned field is correct. Test a second selection, clear the dropdown, and check the behavior for a value that is absent from the source table if users can enter values manually. Confirm that newly added choices are included in both the validation range and the lookup range. Finally, label the dropdown and result columns clearly; a reader should be able to tell what they are choosing and what the returned value means without inspecting the formula.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.