Google Sheets で各行に対応する連動プルダウンを作る

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow. Helper formulas were verified; interactive drop-down behavior and the optional onEdit trigger were not verified.

Which versions this applies to

Google Sheets on the web (data validation rules are created on desktop). The drop-downs work in the mobile apps once they’re set up. This method uses ARRAYFORMULA, VLOOKUP and SEQUENCE only. It doesn’t need LAMBDA, named ranges, INDIRECT or Apps Script.

Why the usual tutorials break on new rows

Most dependent drop-down guides use one of two builds:

1. A named range per category plus INDIRECT in a helper cell for each row.
2. A FILTER formula per row, copied down, for example =TRANSPOSE(FILTER(Lists!B:B, Lists!A:A=A2)).

Both need one helper formula per row. An inserted row picks up the drop-down rule from the row above, but not the helper formula, so its list is empty. Named ranges also need a new range for every new category.

You might try to fix this with one MAP(..., LAMBDA(c, TOROW(FILTER(...)))) formula. Google’s MAP documentation says “Array results for mapped values aren’t supported” and lists the error “Single value expected. Nested array results are not supported.” For dependable drop-down helper ranges, use a single-result mapping or the grid below. A simple fixed-width SPLIT array did expand inside MAP in our live check, but that does not establish support for variable-length FILTER/TOROW lists; this tutorial does not rely on that behavior.

The build below avoids both problems. Every row’s choices come from one lookup over a two-dimensional grid, and each lookup returns a single value.

Example data

Tab Lists (one category/item pair per row, any order):

Column C is a formula (Step 1). You only type columns A and B.

Tab Entry (where people pick values): A1 Category, B1 Item, then rows of data.

Tab Helper: empty. One formula fills it.

Step 1: Number each item within its category

In Lists!C2, enter:

=ARRAYFORMULA(IF(A2:A="", "", A2:A&"|"&COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))))

COUNTIFS counts how many times the category has appeared up to and including each row. That’s a running count, so the third Fruit becomes Fruit|3 even though it’s on row 5. Your list doesn’t need to be sorted, and you can add pairs at the bottom at any time.

Pick a separator that never appears in your data. | is a safe choice for most lists.

Step 2: Make the first drop-down (category)

1. In Lists!E2, create a clean list of categories:

=SORT(UNIQUE(FILTER(A2:A, A2:A<>"")))

2. Select Entry!A2:A1000.
3. Click Data > Data validation > Add rule.
4. Under Criteria, choose Dropdown (from a range) and enter =Lists!$E$2:$E.
5. Click Done.

New categories appear in this drop-down as soon as you type them into Lists, because E2 recalculates.

Step 3: Build every row’s choices with one formula

In Helper!A2, enter:

=ARRAYFORMULA(IFERROR(VLOOKUP(Entry!A2:A1000&"|"&SEQUENCE(1, 20), {Lists!C2:C, Lists!B2:B}, 2, FALSE), ""))

Here’s what it does:

• Entry!A2:A1000 is a column of chosen categories, and SEQUENCE(1, 20) is a row of the numbers 1 to 20. Joining them with & inside ARRAYFORMULA produces a 999 × 20 grid of keys: Fruit|1, Fruit|2 … Fruit|20 on a Fruit row.
• {Lists!C2:C, Lists!B2:B} puts the key column first, so VLOOKUP can return the item from column 2.
• Keys that don’t exist (Fruit|4 and beyond, or |1 on a row with no category) become blanks through IFERROR.

With the sample data, a row where A is Fruit gets Apple | Banana | Cherry in the helper, and a blank row gets nothing.

Adjust two numbers to your data:

• 20 is the most items a category can have. Raise it if needed, and widen the drop-down range in Step 4 to match (20 columns is A:T).
• 1000 is the last entry row. The Helper tab must have at least as many rows as the formula returns. Add rows to it if needed.

Keep the Helper tab empty apart from this formula. Anything typed into it blocks the output: see Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets. To stop collaborators from editing it, right-click the tab and choose Protect sheet, then hide it.

Step 4: Make the second drop-down point at its own helper row

1. Select Entry!B2:B1000 (the whole block at once, not one cell).
2. Click Data > Data validation > Add rule.
3. Choose Dropdown (from a range) and enter:

=Helper!$A2:$T2

4. Under Advanced options, choose Reject the input if only listed items are allowed.
5. Click Done.

The columns are locked with $ and the row isn’t. In one rule applied to B2:B1000, the reference is written for the first cell (B2) and shifts for each row below. B2 reads Helper row 2, B57 reads Helper row 57.

Pick Fruit in A5 and the drop-down in B5 offers Apple, Banana and Cherry. Change A5 to Dairy and B5 offers Milk and Yogurt.

Why this keeps working when you insert rows

Everything in this build is positional:

• When you insert a row inside the Entry data, the reference Entry!A2:A1000 in the helper formula grows to A2:A1001. The helper output grows with it, and row n of the helper still matches row n of Entry.
• The inserted row inherits the validation rule, and the rule still reads “the helper row with my row number”.
• There’s no per-row helper formula that could be left behind.

New items work too: add Fruit, Mango at the bottom of Lists, its key becomes Fruit|4, and every Fruit row shows Mango right away.

Step 5: Catch items that no longer match their category

If someone picks Fruit → Banana and then changes the category to Dairy, “Banana” stays in the cell. Sheets marks it as invalid but doesn’t clear it. Two ways to deal with it:

Highlight mismatches with conditional formatting. Select Entry!B2:B1000, open Format > Conditional formatting, choose Custom formula is, and enter:

=AND($B2<>"", COUNTIFS(INDIRECT("Lists!A2:A"), $A2, INDIRECT("Lists!B2:B"), $B2)=0)

Google’s conditional formatting help says custom formulas can only reference other sheets through INDIRECT, which is why the Lists ranges are wrapped in it.

Clear the item automatically with a small script (optional). Open Extensions > Apps Script, paste this in, and save:

function onEdit(e) {
 const r = e.range;
 if (r.getSheet().getName() !== 'Entry') return;
 if (r.getColumn() !== 1 || r.getRow() < 2 || r.getNumColumns() !== 1) return;
 r.offset(0, 1).clearContent();
}

When a category cell in column A changes, the item next to it is cleared. The getNumColumns() check stops it from wiping column B when someone pastes both columns at once.

Common mistakes

1. Writing =Helper!$A$2:$T$2 in the rule. With the row locked, every row offers the choices for row 2. Remove the $ before the row number.
2. Creating the rule one cell at a time. Apply one rule to the whole block B2:B1000 so the relative reference shifts correctly.
3. Typing INDIRECT or FILTER into the validation box. “Dropdown (from a range)” expects a range. Put formulas in a helper tab and point the rule at the result.
4. Leaving trailing spaces in category names. "Fruit " and "Fruit" produce different keys, so the item list comes back empty. Clean the Lists tab with Data > Data cleanup > Trim whitespace. For stubborn spaces see TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160).
5. A category with more items than the helper is wide. Items beyond number 20 silently don’t appear. Raise the SEQUENCE size and the rule width together.

Excel vs. Google Sheets

Excel’s list validation Source box accepts a reference produced by INDIRECT, such as =INDIRECT(A2) pointing at a named range, or a spilled range such as =$E$2#. It doesn’t accept a formula that returns a dynamic array, like FILTER, directly. Answers on Microsoft Q&A describe this setup. Google Sheets doesn’t accept INDIRECT in the range box either, which is why this build uses a helper tab. ARRAYFORMULA is Sheets-only.

FAQ

Does Google Sheets have a built-in dependent drop-down?

No. Google’s help lists two drop-down types: a list of items you type and a list from a range. Neither can change based on another cell, so you need a helper range that a formula fills in.

Why is my dependent drop-down empty on new rows?

The new row inherited the validation rule but not the helper formula it points to. Use one ARRAYFORMULA for the whole helper, as in this article, so every row is covered automatically.

Can I use INDIRECT in Google Sheets data validation?

Not in the Dropdown (from a range) box, which expects a range. Put INDIRECT, FILTER or VLOOKUP formulas in helper cells and point the drop-down at those cells.

How many items can each category have?

As many as the helper is wide. With SEQUENCE(1, 20) and a rule range of A:T, each category can show up to 20 items. Increase both numbers for longer lists.

Can I make a third level of drop-downs?

Yes. Add a second list with the second-level item in column A and the third-level items in column B, build the same key column, and add a second helper tab that looks up the value chosen in Entry column B.

Related tutorials

• Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods) (cluster hub)
• Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets
• ARRAYFORMULA vs MAP/LAMBDA vs Fill Down in Google Sheets
• Highlight Duplicates Across Two Columns in Google Sheets (Conditional Formatting)
• Extract Email Addresses from Text in Google Sheets with REGEXEXTRACT

Sources

• Google Docs Editors Help: Create an in-cell dropdown list (Dropdown from a range, Reject the input / Show a warning): https://support.google.com/docs/answer/186103
• Google Docs Editors Help: MAP function (array results for mapped values aren’t supported; “Single value expected” error): https://support.google.com/docs/answer/12568985
• Google Docs Editors Help: Use conditional formatting rules in Google Sheets (other sheets need INDIRECT): https://support.google.com/docs/answer/78413
• Google Docs Editors Help: COUNTIF (criteria behavior also used by COUNTIFS): https://support.google.com/docs/answer/3093480
• Microsoft Q&A: Creating a drop-down list that is dependent on another drop-down list (INDIRECT in the Source box): https://learn.microsoft.com/en-us/answers/questions/5195385/creating-a-drop-down-list-that-is-dependent-on-ano
• Microsoft Q&A: Dynamic drop-down list “The Source currently evaluates to an error” (FILTER not accepted directly; reference a spilled range with #): https://learn.microsoft.com/en-us/answers/questions/5273094/dynamic-drop-down-list-the-source-currently-evalua