Google Sheets の ARRAYFORMULA・MAP/LAMBDA・下方向コピー

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.

Which versions this applies to

• Google Sheets: ARRAYFORMULA, MAP, BYROW, BYCOL, SCAN, REDUCE, MAKEARRAY and LAMBDA are all in Google’s function list.
• Excel 365 and Excel 2024: MAP, BYROW, SCAN and LAMBDA exist (Microsoft’s MAP page lists Excel for Microsoft 365 and Excel 2024). There’s no ARRAYFORMULA, because Excel 365 dynamic arrays spill on their own. See the Excel section.
• Excel 2021 and earlier: No MAP or LAMBDA.

Example data

Sheet “Orders”, A1:D7:

Expected line totals (Qty × price): 54, 60, 100, 100, 48, and blank for row 7.

Decision table

How each one works

Fill down

Type =B2*C2 in E2 and double-click the fill handle. Every row gets its own copy with shifted references. It’s the most transparent option, because anyone can click a cell and see its formula. Its weakness: the formula only exists in the rows you filled, so rows added later by a form, an import or a script have none.

ARRAYFORMULA

Google describes ARRAYFORMULA as enabling non-array functions to work with arrays and displaying the results across multiple rows or columns. You give it ranges instead of single cells, and it evaluates the expression for each position:

=ARRAYFORMULA(IF(B2:B="",,B2:B*C2:C))

The IF(B2:B="",,…) guard returns an empty value for blank rows. Without it, an open-ended range like B2:B would fill the rest of the sheet with zeros. Google’s documentation also notes that pressing Ctrl+Shift+Enter while editing adds ARRAYFORMULA( for you, and that many array formulas expand automatically without it.

Header-row pattern: put the header and the formula together in row 1, so sorting and inserting rows can’t break it:

={"Line total";ARRAYFORMULA(IF(B2:B="",,B2:B*C2:C))}

MAP and LAMBDA

MAP calls a LAMBDA once for each value, or for each set of matching values when you pass several ranges. From Google’s MAP documentation: the LAMBDA must have exactly one name per array you pass, otherwise MAP returns #N/A. For predictable output, design each call to return a single value. Although the documentation describes array results as unsupported, the live SPLIT examples below expanded successfully.

=MAP(B2:B,C2:C,LAMBDA(qty,price,IF(qty="",,qty*price)))

qty and price are names you choose. Inside the LAMBDA they hold one row’s values, so you can write normal single-cell logic.

BYROW passes a whole row to the LAMBDA and expects one value back. Use it for “sum, max or count across this row”. SCAN carries a running value from row to row.

5 tasks side by side

Task 1: Row math (line total)

All three approaches work, so pick ARRAYFORMULA for brevity:

=ARRAYFORMULA(IF(B2:B="",,B2:B*C2:C))

Task 2: AND / OR per row — the ARRAYFORMULA trap

Goal: flag rows where Qty > 10 and Status is “Paid”. Rows 2 and 4 should be flagged.

This looks right but is wrong:

=ARRAYFORMULA(IF(AND(B2:B6>10,D2:D6="Paid"),"Bulk paid",""))

AND evaluates all the values together and returns a single TRUE or FALSE for the whole range. Here it returns FALSE, because row 3 fails, so no row gets flagged.

Fix A: multiply for AND and add for OR. Each comparison stays an array:

=ARRAYFORMULA(IF(B2:B="",,IF((B2:B>10)*(D2:D="Paid"),"Bulk paid","")))

Fix B: use MAP so AND sees one row at a time:

=MAP(B2:B,D2:D,LAMBDA(qty,status,IF(qty="",,IF(AND(qty>10,status="Paid"),"Bulk paid",""))))

Fix A is shorter. Fix B reads like the business rule, and it keeps working when you add more conditions such as OR(…), ISNUMBER(SEARCH(…)) or nested IFs.

Task 3: Total across columns for each row

Monthly units in B:D (Jan, Feb, Mar), one product per row. ARRAYFORMULA(SUM(B2:D)) returns one grand total, because SUM aggregates everything it’s given. BYROW does what you meant:

=BYROW(B2:D,LAMBDA(row,IF(COUNTA(row)=0,,SUM(row))))

The pre-LAMBDA workaround multiplies by a column of ones with MMULT. It works, but it’s hard to read:

=ARRAYFORMULA(IF(B2:B="",,MMULT(IF(B2:D="",0,B2:D),SEQUENCE(COLUMNS(B2:D),1,1,0))))

SEQUENCE(COLUMNS(B2:D),1,1,0) builds a column of 1s (one per month), and MMULT multiplies each row by it, which is the same as adding the row.

Task 4: Running total

Fill-down version (in E2, filled down):

=SUM($C$2:C2)

SCAN version (one cell). Google’s function list gives the syntax as SCAN(initial_value, array_or_range, LAMBDA):

=SCAN(0,C2:C6,LAMBDA(total,value,total+value))

total is the running result so far and value is the current row. Starting at 0, the results are 4.5, 24.5, 28.5, 41 and 44.2 for the unit-price column. With an open range like C2:C, the running total keeps repeating down the blank rows, so either bound the range or wrap it in an IF that blanks rows with no data.

Task 5: SUMIFS (or a lookup) for every row

Goal: next to each customer in a summary list (G2:G), total the paid quantity. A per-row SUMIFS is the natural formula:

=MAP(G2:G,LAMBDA(cust,IF(cust="",,SUMIFS(B2:B,A2:A,cust,D2:D,"Paid"))))

Why not ARRAYFORMULA? Some functions don’t expand row by row inside ARRAYFORMULA, and conditional aggregates are where people most often run into this. Whether ARRAYFORMULA(SUMIFS(…, G2:G, …)) returns one value or a column depends on the function. MAP removes the guesswork, because each call gets exactly one customer.

Things that break each approach

ARRAYFORMULA and MAP: blocked output. If anyone types a value anywhere in the output column, the whole formula fails with #REF! “Array result was not expanded”. Clear the cell, and consider protecting the column. Full fix: array result was not expanded.

Fill down: silent drift. An overwritten cell or a formula filled only to row 500 doesn’t show an error. Totals are just quietly wrong. Check with =SUM(MAP(E2:E500,LAMBDA(c,ISFORMULA(c)*1))) against the number of data rows.

MAP / LAMBDA: calculation limits. Each LAMBDA call is a separate calculation. Google’s named functions help page includes “Calculation limit was reached while trying to compute this formula” as a known error, in the context of recursive formulas. To stay clear of it, bound your ranges (B2:B5000 instead of B2:B) when the data size is predictable, and don’t wrap a heavy LAMBDA around tens of thousands of rows if ARRAYFORMULA can do the same job.

MAP: check output shape. In the live Sheets check, =MAP({1;2},LAMBDA(x,SPLIT("a b"," "))) returned a 2-by-2 array of a/b; a BYROW version also expanded. Do not infer that every nested or variable-width array is supported. A single result per call, or TEXTJOIN, remains the predictable design.

What about Excel?

One syntax difference: Microsoft’s MAP page says an invalid LAMBDA or a wrong number of parameters returns #VALUE! (“Incorrect Parameters”), while Google documents #N/A for the same mistake.

Common mistakes

1. Open ranges without a blank guard. =ARRAYFORMULA(B2:B*C2:C) returns 0 for every empty row to the bottom of the sheet. Add IF(B2:B="",,…).
2. AND/OR inside ARRAYFORMULA. They return a single value. Use * for AND and + for OR, or switch to MAP.
3. Mixing approaches in one column. An ARRAYFORMULA in E2 plus hand-typed formulas below it causes #REF!. Pick one.
4. Wrong number of LAMBDA names. MAP(B2:B,C2:C,LAMBDA(x,x*2)) passes two arrays but names only one, so it returns #N/A in Sheets.
5. Assuming every nested array works. Simple fixed-width SPLIT results expanded in the live check, but test the exact shape you need.

FAQ

Is MAP better than ARRAYFORMULA in Google Sheets?

Not in general. ARRAYFORMULA is shorter and fine for row-by-row math. MAP is better when the per-row logic uses AND, OR, SUMIFS or other functions that collapse an array into one value.

Why does AND not work in ARRAYFORMULA?

AND combines every value it receives into a single TRUE or FALSE, so it can’t return one result per row. Multiply the conditions instead, (B2:B>10)*(D2:D=“Paid”), or use MAP with AND inside the LAMBDA.

How do I make a formula apply automatically to new rows in Google Sheets?

Put one ARRAYFORMULA or MAP formula at the top of the column with an open-ended range like B2:B and a blank check. New rows are calculated automatically. Fill-down formulas only exist in rows you filled.

What does BYROW do in Google Sheets?

BYROW applies a LAMBDA to each row of a range and returns one value per row, for example =BYROW(B2:D,LAMBDA(row,SUM(row))) for row totals.

Does Excel have ARRAYFORMULA?

No. Excel 365 spills array results automatically, and Excel Tables fill formulas down as calculated columns. MAP, BYROW and LAMBDA exist in Excel 365 and Excel 2024.

Related tutorials

• Fix “Array result was not expanded because it would overwrite data”
• FILTER vs QUERY in Google Sheets (C2 hub)
• Google Sheets QUERY “Unable to parse query string”
• COUNTIF vs COUNTIFS vs SUMPRODUCT

Live-test findings

The MAP-based ISFORMULA counter returned 5 for five formula cells; SUMPRODUCT(--ISFORMULA(E2:E500)) returned only 1. For Task 3, MMULT and BYROW both returned 19, 24, 31 on numeric monthly columns, with grand total 74. Use numeric month data, not the Orders Status text column. ARRAYFORMULA with SUMIF expanded per criterion in the live check; SUMIFS did not. MAP explicitly calls SUMIFS once per customer.

Sources

• Google Docs Editors Help: ARRAYFORMULA (what it does, Ctrl+Shift+Enter, many array formulas expand automatically): https://support.google.com/docs/answer/3093275
• Google Docs Editors Help: MAP function (one LAMBDA name per array, #N/A otherwise, no array results per mapped value): https://support.google.com/docs/answer/12568985
• Google Docs Editors Help: BYROW function (one value per row, exactly one LAMBDA name): https://support.google.com/docs/answer/12570930
• Google Docs Editors Help: LAMBDA function: https://support.google.com/docs/answer/12508718
• Google Docs Editors Help: Google Sheets function list (SCAN, REDUCE, MAKEARRAY, BYCOL syntax): https://support.google.com/docs/table/25273
• Google Docs Editors Help: Create & use named functions (“Calculation limit was reached” FAQ, named functions with LAMBDA): https://support.google.com/docs/answer/12504534
• Microsoft Support: MAP function (Excel 365 / Excel 2024 availability, #VALUE! “Incorrect Parameters”): https://support.microsoft.com/en-us/excel/functions/map-function
• Microsoft Support: Top ten ways to clean your data (Excel Table calculated columns fill down automatically): https://support.microsoft.com/en-us/excel/top-ten-ways-to-clean-your-data