BYROW Pattern — Row-by-Row Logic

👨‍🏫

Your instructor Puneet Arora Excel & Sheets specialist · aplly.xyz

🎯 What You'll Learn

Explain what BYROW does and how it replaces dragging formulas down
Write BYROW + LAMBDA formulas from scratch on any data range
Use INDEX(row, 1, N) to pick specific columns inside a LAMBDA
Apply LET inside LAMBDA to write clean, readable row formulas
Identify the #CALC! error and know exactly how to avoid it

📋 Before You Begin

Comfortable entering basic formulas (SUM, IF, VLOOKUP)
Familiar with what a cell range like B2:E21 means
Using Excel 365 / Excel 2021+ or Google Sheets (older Excel won't work)
No programming experience needed — LAMBDA is explained from scratch

🌱 Beginner ⏱ ~2 min

The Big Idea — What BYROW Actually Does

Imagine you're a factory quality inspector. Instead of walking the entire floor at once, you go row by row — checking each row on the assembly line, recording your result, then moving to the next. That's exactly what BYROW does with your spreadsheet data.

Your Data Range
e.g. B2:E21

→

BYROW loops
row by row

→

LAMBDA runs
your formula once

→

One result
per row, instantly

✨

The old way vs the new way Old way: Write a formula in row 2, drag it down 1,000 rows. Every cell holds a separate formula. BYROW way: Write ONE formula, press Enter. The results spill automatically into every row below — no dragging ever needed.

🌱 Beginner ⏱ ~1 min

The Data Table We'll Use

All 12 examples in this article use this sales table. Keep it open in another tab as a reference:

	A · Rep	B · Region	C · Units	D · Price	E · Returns
Row 1	Alice	North	150	20	5
Row 2	Bob	South	80	50	12
Row 3	Charlie	East	200	15	2

📌

Column letters are the key When you select a range for BYROW, column positions are relative to your selection, not to the sheet. This is the most important concept in this article — we explain it fully in Section 4.

⚡ Intermediate ⏱ ~3 min

The Core Syntax — Decoded

Every BYROW formula has the same two-part structure. Read the formula like a sentence:

Formula Plain English

=BYROW(C1:D3, LAMBDA(row, INDEX(row, 1, 1) * INDEX(row, 1, 2) ) )

Go through C1:D3, row by row. For each horizontal slice, call it row. Grab the 1st column of that slice (Units). Multiply it by... ...the 2nd column of that slice (Price). Return one result per row.

BYROW(range, …)

You give BYROW a rectangular range. It will visit each row one at a time, like flipping through pages in a book.

LAMBDA(row, …)

LAMBDA defines what to do with each row. The name row is just a label you invent — you could use any name you like.

INDEX(row, 1, N)

INDEX picks the Nth column from the current row slice. This is how you say "give me the Units column" or "give me the Price column."

Excel / Google Sheets

/* Example 1: Total Revenue = Units × Price */
=BYROW(C1:D3, LAMBDA(row,
  INDEX(row,1,1) * INDEX(row,1,2)
))

Result — spills into 3 rows automatically

Alice  → 150 × 20 = 3000
Bob    → 80  × 50 = 4000
Charlie → 200 × 15 = 3000

⚡ Intermediate ⏱ ~3 min

INDEX Inside LAMBDA — The Relative Rule

This is the rule that trips everyone up at first. INDEX counts from the left edge of YOUR selection — not from column A of the sheet.

	If selection is `C1:D3`	If selection is `B1:E3`
1	Column C (Units)	Column B (Region)
2	Column D (Price)	Column C (Units)
3	Out of range!	Column D (Price)
4	Out of range!	Column E (Returns)

⚠️

Practical tip Before writing any BYROW formula, ask yourself: "Which columns am I selecting, and what number is each column in that selection?" Write those numbers down. This one habit prevents 90% of formula errors.

⚡ Intermediate ⏱ ~5 min

12 Live Examples

Click any example to see the formula and how it works. Each one uses a slightly different part of the dynamic array toolkit.

1 · Total Revenue — Units × Price

Range: C1:D3 · col 1 = Units, col 2 = Price

=BYROW(C1:D3, LAMBDA(r, INDEX(r,1,1) * INDEX(r,1,2)))

Multiplies Units × Price for each row. Alice: 150×20 = 3,000. Bob: 80×50 = 4,000. Charlie: 200×15 = 3,000.

2 · Net Units — Units − Returns

Range: C1:E3 · col 1 = Units, col 3 = Returns

=BYROW(C1:E3, LAMBDA(r, INDEX(r,1,1) - INDEX(r,1,3)))

Notice we skip column 2 (Price) entirely. INDEX(r,1,3) jumps straight to the 3rd column of the selection — Returns. Alice: 150−5 = 145. Bob: 80−12 = 68.

3 · Commission — 10% of Revenue

Range: C1:D3 · col 1 = Units, col 2 = Price

=BYROW(C1:D3, LAMBDA(r, (INDEX(r,1,1)*INDEX(r,1,2)) * 0.1))

Calculates Revenue first, then multiplies by 0.1. Alice: 3000×0.1 = 300. Bob: 4000×0.1 = 400.

4 · Status Check — "Goal Met" or "Low"

Range: C1:C3 · col 1 = Units only

=BYROW(C1:C3, LAMBDA(r, IF(INDEX(r,1,1) > 100, "Goal Met", "Low")))

Since we only selected column C, there's only one column — INDEX(r,1,1) = Units. Returns: Alice → Goal Met, Bob → Low, Charlie → Goal Met.

5 · Return Rate — Returns ÷ Units

Range: C1:E3 · col 1 = Units, col 3 = Returns

=BYROW(C1:E3, LAMBDA(r, INDEX(r,1,3) / INDEX(r,1,1)))

Divides Returns by Units. Bob has the highest return rate: 12÷80 = 15%. Alice: 5÷150 = 3.3%.

6 · High Value? — "Premium" or "Budget"

Range: D1:D3 · col 1 = Price only

=BYROW(D1:D3, LAMBDA(r, IF(INDEX(r,1,1) > 25, "Premium", "Budget")))

Only column D is selected. Alice (Price=20) → Budget. Bob (Price=50) → Premium. Charlie (Price=15) → Budget.

7 · Tax Amount — 5% on Revenue

Range: C1:D3 · col 1 = Units, col 2 = Price

=BYROW(C1:D3, LAMBDA(r, (INDEX(r,1,1)*INDEX(r,1,2)) * 0.05))

Same as commission but at 5%. Alice: 3000×0.05 = 150. Bob: 4000×0.05 = 200.

8 · Region Code — First Letter of Region

Range: B1:B3 · col 1 = Region only

=BYROW(B1:B3, LAMBDA(r, LEFT(INDEX(r,1,1), 1)))

Extracts just the first character: North → N, South → S, East → E. Useful for creating short codes or category flags.

9 · Net Revenue — (Units−Returns) × Price

Range: C1:E3 · col 1 = Units, col 2 = Price, col 3 = Returns

=BYROW(C1:E3, LAMBDA(r, (INDEX(r,1,1)-INDEX(r,1,3)) * INDEX(r,1,2)))

The most realistic revenue calc: subtract returns first, then multiply by price. Alice: (150−5)×20 = 2,900. Bob: (80−12)×50 = 3,400.

10 · Bonus Tier — $500 if Units > 150

Range: C1:C3 · col 1 = Units only

=BYROW(C1:C3, LAMBDA(r, IF(INDEX(r,1,1)>150, 500, 0)))

Alice (150) does NOT exceed 150 → 0. Charlie (200) exceeds → 500. Bob (80) → 0. Note: strictly greater than, so 150 itself earns nothing.

11 · Inventory Alert — "Review" if Returns > 10%

Range: C1:E3 · col 1 = Units, col 3 = Returns

=BYROW(C1:E3, LAMBDA(r, IF(INDEX(r,1,3) > (INDEX(r,1,1)*0.1), "Review", "OK")))

Checks if Returns > 10% of Units. Bob: 12 > 8? → Review. Alice: 5 > 15? → OK. Charlie: 2 > 20? → OK.

12 · Full Label — Joins Region & Units

Range: B1:C3 · col 1 = Region, col 2 = Units

=BYROW(B1:C3, LAMBDA(r, INDEX(r,1,1) & "-" & INDEX(r,1,2)))

The & operator joins text. Alice: "North-150". Bob: "South-80". Great for creating unique identifiers or display labels.

⚡ Intermediate ⏱ ~2 min

LET + LAMBDA — Cleaner, Readable Formulas

Once your formulas have 3+ INDEX calls, they become hard to read. LET solves this by giving names to each column — like labelling boxes before you use them.

Without LET (hard to read) With LET (easy to read)

=BYROW(C1:E3, LAMBDA(row, (INDEX(row,1,1) - INDEX(row,1,3)) * INDEX(row,1,2) ) )

=BYROW(C1:E3, LAMBDA(row,
  LET(
    u, INDEX(row,1,1),
    p, INDEX(row,1,2),
    r, INDEX(row,1,3),
    (u - r) * p
  )
))

u = Units, p = Price, r = Returns Formula now reads like: Net Revenue = (Units − Returns) × Price

🏷️

Name your columns

Give each INDEX call a short name inside LET. Use u, p, r — or full words like units.

🔄

Reuse without repeating

Need Units twice? With LET, write u twice. Without LET, write the full INDEX call twice.

🐛

Easier to debug

When something breaks, readable names make it immediately obvious which column is wrong.

🔴 Advanced ⏱ ~1 min

The #CALC! Trap

BYROW has one strict rule: every row must produce exactly one value. If your LAMBDA tries to return two or more values from a single row, you get a #CALC! error.

✅

This works

Each row → one number, one text, one TRUE/FALSE. One answer = no problem.

❌

This breaks

Trying to return Units AND Price in one row? BYROW can't handle two-column output. It throws #CALC!.

💡

The fix Need two columns of output? Write two separate BYROW formulas — one for each column. Place them side by side. Each formula stays clean and each produces one column.

⚡ Intermediate ⏱ ~3 min

Quiz — Test Yourself

Five questions. Each one tests whether you can apply the BYROW pattern, not just remember it.

Score 0 / 5

You select the range D1:E3 for BYROW. What does INDEX(r, 1, 2) refer to?

Which formula correctly calculates commission (10% of Units × Price) using range C1:D3?

What error will you get if your LAMBDA tries to return both Units and Price as two separate values in one row?

You want to label rows as "Alert" if Returns > 10% of Units. Range is C1:E3. Which formula is correct?

What is the main advantage of using LET inside LAMBDA in a BYROW formula?

💡 Key Takeaways

BYROW loops your formula row by row — one formula replaces dragging down 1,000 cells.
The Relative Rule — INDEX(r,1,N) counts from the left edge of YOUR selection, not from column A.
LAMBDA names your row — the variable (e.g. row) represents one horizontal slice at a time.
LET makes it readable — name each INDEX call once, use the name as many times as needed.
One output per row, always — trying to return two values causes #CALC!. Use two separate BYROW formulas instead.