How to Auto-Fill Other Cells After Selecting a Value from a Drop-Down List in Excel: A Comprehensive Guide
When working with Excel, automating data entry can significantly boost productivity. One common task is auto-filling related data after selecting a value from a drop-down list. This guide explores four comprehensive methods, ranging from Excel native functions to VBA and third-party tools like Kutools.
First of All: Create a Drop-Down List
Method 1: Auto-Fill Using the VLOOKUP Function
Method 2: Auto-Fill Using INDEX and MATCH Functions
Method 3: Auto-Fill Using Kutools for Excel
Method 4: Auto-Fill Using a Defined Function
Method 4: Auto-Fill Using a Defined Function
First of All: Create a Drop-Down List
Before you implement any auto-fill method, you need a drop-down list in place. This drop-down acts as the trigger for filling in related cells.
Steps:
Step 1. Prepare the data source.
Step 2. Create the Drop-down list.
Go to the cell where you want the drop-down (e.g., Sheet1!D2)
Navigate to Data > Data Validation > Data Validation.
In the Data Validation dialog, choose List from the Allow section, and select the data source. Click OK.
Once your drop-down is in place, you can proceed to implement any of the following auto-fill methods.
Method 1: Auto-Fill Using the VLOOKUP Function
VLOOKUP is one of the most commonly used functions for data retrieval in Excel. When paired with a drop-down, it can quickly fetch related data from a reference table.
Steps:
In the adjacent cell of the drop-down list (e.g., E2), enter:
🔓 Formula explanation:
- Looks for the value in D2 in the first column of A2:B5. If found, it returns the corresponding value from column 2 (B column). If not found, it gives an error (#N/A).
- FALSE means it must be an exact match.
Step 2. Press Enter key.
✨ Notes
- Use IFERROR() to hide errors if no value is selected:
=VLOOKUP(D2,$A$2:$B$5,2,FALSE) - Can’t search to the left of the key column.
Method 2: Auto-Fill Using INDEX and MATCH Functions
INDEX and MATCH is a powerful duo that surpasses VLOOKUP in flexibility. It supports left-side lookups and remains stable even if columns are rearranged.
Steps:
In the adjacent cell of the drop-down list (e.g., E2), enter:
🔓 Formula explanation:
- MATCH(D2, $A$2:$A$5, 0)
Searches for D2 in the range A2:A5. 0 means exact match (like FALSE in VLOOKUP).
Returns the position (row number) where D2 is found. - INDEX($B$2:$B$5, ...)
Takes the row number from MATCH.
Returns the corresponding value from B2:B5.
Step 2. Press Enter key.
✨ Notes
- Both the return range (INDEX) and the lookup range (MATCH) must align row-wise.
- Can search left or right.
- More durable than VLOOKUP.
Method 3: Auto-Fill Using Kutools for Excel
Kutools offers a GUI-based approach that eliminates the need for formulas. It’s especially useful for users who want quick results without diving into Excel functions.
Steps:
Step 1. In the adjacent cell of the drop-down list (e.g., E2), go to Kutools > Formula Helper > Lookup & Reference > Look for a value list.
Step 2. Select Table array, Lookup value and the Column Number. Click OK.
✨ Notes
- Kutools allows you to apply this to a whole range at once.
- The tool is very beginner-friendly and reduces manual errors.
- Easy to use.
- No formulas required.
Tired of repetitive tasks and complex formulas in Excel? Kutools for Excel is your all-in-one productivity booster! With over 300 powerful features—batch editing, smart filling, auto filtering—you'll work 10x faster. Download now and take your Excel skills to the next level!
Method 4: Auto-Fill Using a Defined Function
For users needing a dynamic and automated solution beyond the limits of formulas, VBA provides maximum control and logic customization.
Steps:
Step 1. Press Alt +F11 keys to open VBA editor.
Step 2. Click Insert > Module.
Step 3. Paste below code to the Module.
'Update by Extendoffice
Function GetProductInfo(productName As String, colIndex As Integer) As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'the sheet that the data source in
Dim rng As Range
Set rng = ws.Range("A2:B5") 'the range of data source
Dim r As Range
For Each r In rng.Rows
If r.Cells(1, 1).Value = productName Then
GetProductInfo = r.Cells(1, colIndex).Value
Exit Function
End If
Next
GetProductInfo = "Not found"
End Function
Step 4. Return to the sheet and in the adjacent cell of the drop-down list (e.g., E2), enter:
Step 5. Press the Enter key.
✨ Notes
- Requires a macro-enabled workbook (.xlsm)
Frequently Ask Questions
Q1: What if my data range changes frequently?
Use named ranges or dynamic tables to maintain references.
Q2: Can I use VLOOKUP for leftward lookups?
No, consider using INDEX+MATCH or Kutools for that case.
Q3: Is Kutools safe to use?
Yes, it's widely used and trusted, but always download from the official website.
Q4: Will VBA work in all Excel versions?
Most desktop versions support it, but it’s disabled by default and unsupported in Excel Online.
Q5: Is Kutools free to use?
Kutools for Excel is not a fully free tool, but it offers a free trial, followed by a one-time purchase option:
- 30‑day free trial with full functionality—no credit card required.
- Perpetual license for a single user: around US $49, including 2 years of free updates and support.
- After the 2-year support period, you can continue using the version you have indefinitely—just without further updates.
Best Office Productivity Tools
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in