Excelで「複数列がすべて0」かつ「特定の文字列を除外（<>）」した行だけを対象に、INDEX/MATCHで“次の行”を探すなら、条件を文字列連結やANDのネストで組むより、論理値の掛け算で1/0の配列を作る方法が読みやすくて拡張も簡単です。
やりたいこと：複数列が0の行を見つけ、さらに特定文字列の行を除外したい
現場でよくあるのが、ログや工程表、在庫移動などのデータから「次に同じ状態になる行」「次のイベントまでの差分」を取りたいケースです。今回の条件は次のとおりです。
- 列2・列3・列4がすべて0
- さらに列5が「Warehouse Upgrade」ではない（列5が「Warehouse Upgrade」の行は検索対象から外す）
この2つを同時に満たす行のうち、現在行の次（下方向）にある最初の行を見つけて、INDEXで取り出したい、というのがゴールです。
連結（0&0&0）やANDのネストがつらくなる理由
複数条件を一気に満たす行を探そうとすると、ありがちな実装は次の2パターンです。
| 手法 | よくある書き方 | 困りがちな点 |
|---|---|---|
| 文字列連結で判定 | (B=0)&(C=0)&(D=0) のように連結して "TRUEFALSE..." を比較 | 意図が読み取りづらく、0以外の条件が混ざると破綻しやすい |
| ANDのネスト | AND(AND(AND(...))) | 条件が増えるほど式が横に長くなり、追加・修正でミスが出やすい |
特に「列5は除外（<>）したい」ような条件が混ざると、式の可読性が一気に落ちます。さらに、行範囲（次の20行など）が変わるたびに参照を直す必要が出て、保守が重くなります。
解決のコア：論理値（TRUE/FALSE）を“掛け算”で合成する
Excelでは、(条件) は TRUE/FALSE を返し、計算の文脈では多くの場合 TRUE=1 / FALSE=0 として扱えます。そこで次のように条件を掛け算で束ねます。
(条件1) * (条件2) * (条件3) * ...掛け算のポイントはシンプルで、全部TRUEのときだけ1、どれか1つでもFALSEなら0になります。つまり「AND条件の塊」を読みやすい形で作れます。
| 列2=0 | 列3=0 | 列4=0 | 列5<>”Warehouse Upgrade” | 掛け算結果 | 意味 |
|---|---|---|---|---|---|
| TRUE(1) | TRUE(1) | TRUE(1) | TRUE(1) | 1 | 条件を満たす（検索対象） |
| TRUE(1) | TRUE(1) | TRUE(1) | FALSE(0) | 0 | 除外文字列なので対象外 |
| TRUE(1) | FALSE(0) | TRUE(1) | TRUE(1) | 0 | どれかが0ではないので対象外 |
この「1/0の配列」が作れたら、あとは MATCH(1, 条件配列, 0) で最初に1になる位置を探し、INDEXで必要な列を引くだけです。
基本形：MATCH(1, 条件配列, 0) で“最初に条件を満たす行”を見つける
まずは“条件配列”だけを単独で考えると理解が速いです。たとえば A2:E10 にデータがあり、3行目以降（A3:E10）を検索対象にするなら、条件配列は次の形になります。
(B3:B10=0) * (C3:C10=0) * (D3:D10=0) * (E3:E10<>"Warehouse Upgrade")この式は、B～Eの各行について「条件を満たすなら1、満たさないなら0」を返す配列になります。次に、その配列の中から最初の1の位置を取ります。
MATCH(1, (B3:B10=0)*(C3:C10=0)*(D3:D10=0)*(E3:E10<>"Warehouse Upgrade"), 0)返ってくるのは「検索範囲内での相対位置（1～）」です。ここまで分かれば、INDEXと組み合わせて「該当行のA列」を取り出せます。
INDEX(A3:A10,
MATCH(1,
(B3:B10=0)*(C3:C10=0)*(D3:D10=0)*(E3:E10<>"Warehouse Upgrade"),
0
)
)質問の形を保ったまま：R1C1形式で“次の行”を探す式
「現在行の次（下方向）の20行」を探す、という発想はR1C1形式だと非常に書きやすいです。質問の意図（現在行の条件判定→次の該当行を探す）を保ったまま整理すると、次のようになります。
{=IFNA(
IF((RC2=0)*(RC3=0)*(RC4=0)*(RC5<>"Warehouse Upgrade"),
INDEX(R[1]C1:R[20]C1,
MATCH(1,
(R[1]C2:R[20]C2=0)*
(R[1]C3:R[20]C3=0)*
(R[1]C4:R[20]C4=0)*
(R[1]C5:R[20]C5<>"Warehouse Upgrade"),
0
)
)-RC1,
""
),
"")}この式でやっていることを、役割ごとに分解すると次のとおりです。
| ブロック | 式の部分 | 役割 |
|---|---|---|
| 現在行が対象か | (RC2=0)*(RC3=0)*(RC4=0)*(RC5<>"Warehouse Upgrade") | 現在行が条件を満たすときだけ、下方向検索を実行する（不要なら外してOK） |
| 検索対象範囲 | R[1]C?:R[20]C? | 次の行から20行先までを検索対象にする |
| 条件配列 | (...=0)*(...=0)*(...=0)*(...<>"Warehouse Upgrade") | “全部満たす行だけ1”の配列を作る |
| MATCH | MATCH(1, 条件配列, 0) | 最初に1になる位置（相対行）を取る |
| INDEX | INDEX(R[1]C1:R[20]C1, 位置) | 該当行のC1（列1）を返す |
| 差分 | ... - RC1 | 該当行の列1と現在行の列1の差を出す（不要なら削除） |
| IFNA | IFNA( ..., "" ) | 該当行がないとき #N/A を空欄にする |
「次の20行」という上限がある場合、このR1C1の書き方は範囲がズレにくく、コピペにも強いのがメリットです。逆に、検索対象が数千行に及ぶなら、後述の“テーブル化”や“補助列”が効いてきます。
A1形式で考えたい人向け：セル参照で同じ発想にする
R1C1に慣れていない場合は、A1形式でも同じロジックで整理できます。例として、A列が返したい値、B～Dが0判定、Eが除外文字列だとします。現在行が2行目で、次の20行（3～22行目）から探すなら、次のようになります。
{=IFNA(
IF(($B2=0)*($C2=0)*($D2=0)*($E2<>"Warehouse Upgrade"),
INDEX($A$3:$A$22,
MATCH(1,
($B$3:$B$22=0)*
($C$3:$C$22=0)*
($D$3:$D$22=0)*
($E$3:$E$22<>"Warehouse Upgrade"),
0
)
)-$A2,
""
),
"")}この形にしておくと、「次の30行に伸ばす」「列6も条件に足す」といった変更が、掛け算の項目を増やすだけで済みます。
Microsoft 365ならLETで“読みやすさ”をもう一段上げられる
条件自体がスッキリしても、範囲参照が長いと式が読みにくいことがあります。Microsoft 365 / Excel 2021以降の LET を使うと、範囲や条件を変数化でき、保守性が上がります（同じ範囲を何度も書かないので、修正点も減ります）。
=LET(
id, RC1,
a, R[1]C1:R[20]C1,
b, R[1]C2:R[20]C2,
c, R[1]C3:R[20]C3,
d, R[1]C4:R[20]C4,
e, R[1]C5:R[20]C5,
cond, (b=0)*(c=0)*(d=0)*(e<>"Warehouse Upgrade"),
IFNA(INDEX(a, MATCH(1, cond, 0)) - id, "")
)LETの利点は、「何を検索しているのか」が式の冒頭で説明される点です。あとから見返したときも、変数名（a,b,c…でも良いですが、業務なら amount, qty, status など）で意図を残せます。
さらに、365なら INDEX/MATCH の代わりに XLOOKUP / XMATCH を使うと式が短くなることもあります。
| やりたいこと | 365向けの書き方例 | ポイント |
|---|---|---|
| 該当行の列1を返す | =LET(cond,(b=0)*(c=0)*(d=0)*(e<>"Warehouse Upgrade"), XLOOKUP(1, cond, a, "" ,0)) | “最初の1”を直接引けるので MATCH が不要になる |
| 位置（相対行）だけ欲しい | =LET(cond,(b=0)*(c=0)*(d=0)*(e<>"Warehouse Upgrade"), XMATCH(1, cond, 0)) | 位置が取れれば、INDEXや行番号計算にも使える |
| 該当行が複数あるので全部見たい | =FILTER(a, (b=0)*(c=0)*(d=0)*(e<>"Warehouse Upgrade")) | “一覧で確認”でき、デバッグにも便利 |
除外条件（<>）の落とし穴：実務データは“見た目どおり”ではない
RC5<>"Warehouse Upgrade" は強力ですが、実データでは次のような“見えない違い”で判定がズレることがあります。
- 前後スペース：
"Warehouse Upgrade "（末尾にスペース）が混ざる - 全角・半角：
"Warehouse Upgrade"（全角スペース） - 表記ゆれ：
"Warehouse upgrade"のような大小文字の違い（Excelの通常比較は大小文字を区別しません） - 別名やコード：同じ意味の別文字列が存在する
対策としておすすめなのは、式を複雑にする前にデータを正規化することです。たとえば入力側で TRIM（前後スペース除去）や置換を行う、Power Queryでクレンジングしてから取り込む、などです。どうしても式側で吸収するなら、次のように書けます。
(TRIM(R[1]C5:R[20]C5)<>"Warehouse Upgrade")ただし TRIM を大量行に対して毎回実行すると重くなるため、補助列（クレンジング済み列）を1本作ってそこで整形し、検索条件は整形済み列に対してかける方が高速で安全です。
また、大小文字を区別して除外したい場合は EXACT を使います。EXACTは完全一致（大小文字も区別）なので、除外判定は次のようにします。
--NOT(EXACT(R[1]C5:R[20]C5, "Warehouse Upgrade"))--（二重マイナス）は TRUE/FALSE を 1/0 に強制変換する定番テクニックです。掛け算でも暗黙変換されますが、EXACTやNOTを挟むときは明示しておくと意図が伝わりやすくなります。
条件の追加・OR条件・空白を0扱いにしたいときの拡張パターン
掛け算による条件合成は、条件が増えても破綻しにくいのが強みです。よくある追加要件を、具体的な式の形でまとめます。
| 追加したい条件 | 追加する式のパーツ | 考え方 |
|---|---|---|
| 列6が空白の行だけ | *(R[1]C6:R[20]C6="") | そのまま“掛け算の項目”を増やす |
| 列2が0または空白をOKにしたい | *((R[1]C2:R[20]C2=0)+(R[1]C2:R[20]C2="")>0) | ORは足し算で表現し、最後に>0でTRUE/FALSEへ戻す |
| 列5が2種類の除外ワードを含む | *ISERROR(SEARCH("Upgrade", R[1]C5:R[20]C5)) | 部分一致の除外は SEARCH + ISERROR が定番（ただし重くなるので注意） |
| 列の値が「0」文字列として入っている | *(--(R[1]C2:R[20]C2)=0) | 数値化してから比較する（データ型の不一致を潰す） |
ここで大事なのは、ANDは掛け算、ORは足し算という整理です。ルールが一貫しているので、式が長くなっても迷子になりにくくなります。
大きい表でも遅くしないコツ：範囲と計算回数を管理する
INDEX/MATCHの配列条件は便利ですが、範囲を広げすぎると計算が重くなります。特に B:B のような列全体参照を複数条件で使うと、行数分の配列計算が走りやすくなります。実務で安定させるコツをまとめます。
- 検索範囲を必要最小限に絞る：今回のように「次の20行」など上限があるなら、その範囲に固定する
- テーブル（Ctrl+T）を使い、構造化参照で可読性を上げる：列名で条件が書けるので、式の意味が残る
- 補助列でフラグを作る：条件式を1回だけ計算し、MATCHはそのフラグ列を見る
- TRIM/SEARCHなど重い関数は“都度判定”にしない：データクレンジング側に寄せる
- 揮発性関数（OFFSETなど）に依存しない：再計算が増えやすいので、可能ならINDEXベースにする
補助列フラグは、デバッグのしやすさも大きなメリットです。例えばF列に次のようなフラグを作ります（行ごとに1/0）。
=(B2=0)*(C2=0)*(D2=0)*(E2<>"Warehouse Upgrade")あとは「次の行から下」を探す場合、MATCHの対象がフラグ列だけになり、式がかなり短くなります。複数人で運用するファイルほど、補助列の価値は上がります。
#N/Aや意図しない行が返るときのチェックポイント
配列条件のINDEX/MATCHでつまずくのは、ほとんどが「データの状態」か「範囲ズレ」です。よくある原因と見直し順をまとめます。
| 症状 | 原因になりやすい点 | 確認・対策 |
|---|---|---|
| #N/Aになる | 条件を満たす行が範囲内にない／除外条件が厳しすぎる | まず FILTER（365）や補助列で「1になる行があるか」を可視化する |
| 違う行がヒットする | 検索範囲（R[1]～）が想定とズレている | 「どの行から検索しているか」を再確認。R1C1なら相対参照のずれを疑う |
| 0のはずが一致しない | 数値の0ではなく文字列”0″、または空白 | --で数値化して比較する、または入力ルールを統一する |
| 除外文字列が効かない | 末尾スペース、全角スペース、表記ゆれ | TRIMや置換、Power Queryで正規化。必要なら EXACT で大小文字も含めて設計 |
Excelの「数式の検証」や「評価」も有効ですが、配列条件は一度“条件配列だけ”を別セルに出して目視するのが最短ルートです。例えば同じ条件式をそのまま入力して、1/0が想定どおり並ぶかを見るだけで、原因の切り分けが一気に進みます。
まとめ：INDEX/MATCHの複数条件は“掛け算”で整理すると強い
列2～4がすべて0、さらに列5が特定文字列ではない（<>）行だけを対象に「次の行」を探すなら、条件を連結したりANDをネストするより、論理値の掛け算で条件配列を作るのが読みやすく拡張も簡単です。
- AND条件は
(条件1)*(条件2)*...で合成 - 条件配列ができたら
MATCH(1, 条件配列, 0)で最初の該当行 - INDEXで必要な列を返し、必要なら差分（-RC1など）も同じ式内で出せる
- 実務では除外文字列の表記ゆれやスペースに注意し、可能ならデータ側で正規化する
この型を覚えておくと、「除外条件が増えた」「OR条件も混ざった」「検索範囲を動的にしたい」といった追加要件にも、式を壊さずに積み上げられます。まずは条件配列を掛け算で作り、MATCHで1を探す、という流れで組み立ててみてください。
コメント