「目視で探しますか？」数百行のデータから最長文字列を一瞬で抽出する方法
顧客レビューが500件たまったとき、「★5のレビューの中で一番長いコメントを見たい」と思ったことはないでしょうか。
スクロールしながら目視で探す？ 文字数を別列に書き出して並べ替える？ どちらも現実的ではありません。
実は、LEN・MAX・XLOOKUP（または INDEX+MATCH）の組み合わせを使えば、何千行あっても一瞬で条件付き最長文字列を取り出せます。この記事では、コピペしてすぐ動く数式と、その仕組みをステップバイステップで解説します。
サンプルデータ
以下のデータを使って解説します。意図的に同じ文字数の商品を含めています（理由は後述）。
| A列（カテゴリ） | B列（商品名） |
|---|---|
| 果物 | りんご（3文字） |
| 野菜 | ブロッコリー |
| 果物 | マンゴスチン（6文字） |
| 野菜 | きゅうり |
| 果物 | スイカ（3文字） |
| 野菜 | とうもろこし |
| 果物 | パイナップル（6文字） |
目標：「果物」カテゴリの中で最も文字数が長い商品名を取得する。
解法① XLOOKUP を使う方法（Excel 365 / 2021以降）
完成形の数式
=XLOOKUP(MAX(IF(A2:A8="果物",LEN(B2:B8))),IF(A2:A8="果物",LEN(B2:B8)),B2:B8)
⚠️ Excel 365では
Enterのみで確定できます。Excel 2019以前ではCtrl + Shift + Enterで配列数式として入力してください。
ステップで理解する仕組み
ステップ1：条件を満たす行だけ文字数を返す
IF(A2:A8="果物", LEN(B2:B8))
カテゴリが「果物」なら文字数を、それ以外は FALSE を返します。
結果のイメージ：{3, FALSE, 6, FALSE, 3, FALSE, 6}
ステップ2：最大文字数を取得する
MAX(IF(A2:A8="果物", LEN(B2:B8)))
FALSE は数値計算で無視されるため、果物の最大文字数 6 が得られます。
ステップ3：XLOOKUP で商品名を取得する
最大文字数 6 を、果物の文字数リストから検索し、対応する商品名「マンゴスチン」（最初にヒットした値）を返します。
⚠️ 重要：同じ文字数が複数ある場合の動作
上記のサンプルでは、「マンゴスチン」と「パイナップル」がどちらも6文字です。この場合、数式はデータの上から最初にヒットした「マンゴスチン」を返します。
これは仕様であり、バグではありません。ただし業務によっては「同点の全候補を返したい」ケースもあります。その場合は FILTER 関数との組み合わせが有効ですが、別記事で詳しく紹介します。
解法② INDEX + MATCH を使う方法（全バージョン対応）
XLOOKUP が使えないExcel 2016・2019環境ではこちらを使います。
完成形の数式
=INDEX(B2:B8,MATCH(MAX(IF(A2:A8="果物",LEN(B2:B8))),IF(A2:A8="果物",LEN(B2:B8)),0))
⚠️ Excel 365以外では必ず
Ctrl + Shift + Enterで確定してください。
数式の構造
| 役割 | 数式パーツ |
|---|---|
| 条件付き文字数配列の作成 | IF(A2:A8="果物", LEN(B2:B8)) |
| 最大文字数の取得 | MAX(...) |
| 最大値の行番号を特定 | MATCH(最大値, 文字数配列, 0) |
| 行番号から商品名を返す | INDEX(B2:B8, 行番号) |
考え方はXLOOKUP版とまったく同じです。MATCH で「何行目か」を調べ、INDEX で値を取り出すという2段階になっているだけです。
よくあるエラーと対処法
エラー①：#N/A が返る
原因：検索対象に一致するカテゴリが存在しない（スペルミス・全角半角の違いなど）。
対処：IFERROR でラップして、エラー時のメッセージを設定します。
=IFERROR(XLOOKUP(MAX(IF(A2:A8="果物",LEN(B2:B8))),IF(A2:A8="果物",LEN(B2:B8)),B2:B8),"該当なし")
エラー②：配列数式として入力し忘れている（Excel 2019以前）
原因：Enter だけで確定すると、配列として処理されず正しい結果が返りません。
対処：数式を選択した状態で Ctrl + Shift + Enter を押し直し、数式バーに {=...} の波括弧が表示されることを確認してください。
エラー③：MAX が 0 を返す
原因：条件に一致する行がゼロ件のとき、IF がすべて FALSE を返し、MAX が 0 になります。
対処：上記の IFERROR と組み合わせることで対処できます。
実務での応用例
5つ星レビューの中で最も長いコメントを取得する
=IFERROR(
XLOOKUP(
MAX(IF(D2:D500="★5", LEN(E2:E500))),
IF(D2:D500="★5", LEN(E2:E500)),
E2:E500
),
"該当レビューなし"
)
- D列：星評価（★1〜★5）
- E列：レビューコメント本文
- 出力：★5レビューのうち最も長いコメント全文
部署別に最長メモを書いた社員名を取得する
=IFERROR(
INDEX(A2:A50,
MATCH(
MAX(IF(B2:B50="営業部", LEN(C2:C50))),
IF(B2:B50="営業部", LEN(C2:C50)),
0
)
),
"該当者なし"
)
- A列：社員名、B列：部署名、C列：業務メモ
- 出力：営業部の中で最も長いメモを書いた社員の名前
まとめ：なぜこの組み合わせが強いのか
| 方法 | 数式パターン | 対応バージョン |
|---|---|---|
| XLOOKUP版 | XLOOKUP(MAX(IF(...)), IF(...), ...) |
Excel 365 / 2021以降 |
| INDEX+MATCH版 | INDEX(..., MATCH(MAX(IF(...)), IF(...), 0)) |
全バージョン |
この手法が強力な理由は、LEN によって「文字列」という比較しにくいデータを「数値」に変換している点にあります。文字列のままでは大小比較できませんが、文字数（数値）に変換することで MAX が使えるようになります。
「文字列 → 数値化 → MAX → 逆引き」という流れは、最長・最短だけでなく、条件付きの最大・最小値を取得するあらゆる場面に応用できます。ぜひ自分のデータで試してみてください。
本記事はLookupシリーズの一部です。関連記事もあわせてご覧ください。
