如何基于Excel单元格Y2的下拉选择批量填充Y3:Y13对应列数据？

Hey Mary, nice to help you out with this! Since you already know VLOOKUP, let's shift gears to functions that handle this batch column pull more smoothly—no need to copy formulas manually (or even at all, if you're on a newer Excel version).

Here are two reliable methods tailored to your setup:

Method 1: INDEX + MATCH (Works for All Excel Versions) #

This is the go-to non-volatile solution (meaning it won't slow down your workbook unnecessarily).

In cell Y3, enter this formula:

=INDEX($V$3:$X$13,ROW()-2,MATCH($Y$2,$V$3:$X$3,0))

Then drag the fill handle down from Y3 to Y13 to apply it to the entire range.

Breakdown of the formula: #

• $V$3:$X$13: This is your full data range (all three columns you might pull from). The absolute references ($) ensure the range doesn't shift when you drag the formula down.
• ROW()-2: Calculates the relative row number within your data range. For Y3, ROW() returns 3, minus 2 gives 1—so it grabs the first row of your data range (V3/W3/X3). For Y4, it grabs the second row, and so on.
• MATCH($Y$2,$V$3:$X$3,0): Finds which column corresponds to your selection in Y2. It looks for the exact match of Y2's value in the header row (V3:X3) and returns the column number (1 for V, 2 for W, 3 for X).

Method 2: Dynamic Array Formula (Excel 365/2021+) #

If you're using a modern Excel version with dynamic array support, you can do this in one step—no dragging required!

In cell Y3, just enter this formula and hit Enter:

=INDEX(V3:X13,,MATCH(Y2,V3:X3,0))

Excel will automatically "spill" the formula results down to Y13, filling the entire range instantly. The two commas in INDEX(V3:X13,,...) tell Excel to include all rows from the selected column.

Bonus: Quick Check for Your Dropdown #

Just to make sure everything lines up, confirm your Y2 dropdown is set up with Data Validation > List where the source is $V$3:$X$3—this ensures the options match exactly with your header cells, so the MATCH function works flawlessly.

Either method will let you switch between TIC2, LP3, and MAT4 in Y2, and Y3:Y13 will update automatically to show the corresponding column's values.

内容的提问来源于stack exchange，提问作者Mary