Skip to main content

How to VLOOKUP with drop down list in Excel?

Author: Sun Last Modified: 2024-10-08
Dropdown in Excel
In Excel, VLOOKUP and drop down list are two useful functions. However, have you tried to VLOOKUP with drop down list? For example, you have a drop down list in a range, and when you select one kind of the fruits from the drop down list, the relative price will be shown in the adjacent cell as below screenshot shown. Now this tutorial will tell you VLOOKUP with drop down list.
VLOOKUP with drop down list

arrow blue right bubble VLOOKUP with drop down list

Actually, the VLOOKUP function also work when the look up value is in a drop down list.

For example, you have a range of data as below screenshot shown:

Data example

And then you create a drop down list based on above data. See below screenshot:

Dropdown list created based on the data

Now you want to use VLOOKUP to get the relative price of selected fruit.

1. Select a value from the drop down list, the type this formula =VLOOKUP(E2,$A$2:$B$6,2,FALSE) into a cell adjacent the drop down list. See screenshot:

Formula entered beside the dropdown cell

2. Then click Enter key and the relative price is shown. See screenshot:

Result displayed according to the item selected from the dropdown

Tip:

In the above formula, E2 is the lookup value in the drop down list, A2:B6 is the data range of the original data, 2 indicates this formula will return the values in the second column of original data.


Easily create a dynamic 2-level or multiple-level dependent drop-down list in Excel

In Excel, to create a 2-level or multiple level drop-down list is complex. Here the Dynamic Drop-down List utility of Kutools for Excel can do you a favor. What you only to do is order the data as example shown, then select the data range and output range, then leave it to the utility.  Click for 30 days free trial!
Kutools' Dynamic Drop-down List function
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!