本記事では、Excel 2021を使用して検証および画像の作成を行っています。
通常、値の検索にはVLOOKUPやXLOOKUP、INDEX/MATCHが使われます。
しかし、これらが使えない状況や、あえて別の方法を試したい場合もあるでしょう。
この記事では、実用性は度外視し、
多様なアプローチを知ることで関数の理解を深めることを目指します。
知的好奇心を満たす、少しマニアックなExcel関数パズルです。
今回は「商品名」から「商品ID」を検索する7つの方法に、関数縛りで挑戦します！
今回のミッション
現在の状態とゴール
現在の状態: Excelシートに商品リストがあります。
目指すゴール: 指定した「商品名」（例: “定規”）に対応する「商品ID」（例: “A005“）を関数のみで取得します。
ルール：関数以外使用禁止＆段階的関数縛り！
1. 関数のみ: 使用するのはExcel関数のみ。VBAは使いません。
2. 元データ不変: 元データの変更・加工は禁止です。(名前を付ける等)
3. 初期禁止関数: XLOOKUP, INDEX, MATCH は最初から使用禁止。
4. Excel 2021準拠: Excel 2021で利用可能な関数のみ使用します。
5. 段階的禁止: 方法ごとに、使用禁止関数を追加する縛りプレイです。
データの準備
データの準備手順
1. まず、サンプルファイル excel_de_himatsubushi011.xlsx（10KB）をダウンロードしてください。
2. 次に、検索条件（商品名）を入力するセルとして、F3に「定規」と入力します。
3. 最後に、結果表示セルとしてG列を使用。G3からG9に各数式を入力します。
準備はよろしいですか？定番関数が封印された、値検索の世界へ進みましょう！
ご自身で似たような表を作っていただいてもOKです！
方法1：王道（？）VLOOKUPとCHOOSEの合わせ技！
禁止関数: XLOOKUP, INDEX, MATCH
はじめに、VLOOKUPに一工夫加えます。
通常、検索値は範囲の左端列にある必要がありますが、
今回はB列で検索しA列（つまり、左側！）を取得します。
以下の記事で、VLOOKUP関数で左側の値を取得する方法を解説しています！
考えかた
VLOOKUP関数の 範囲 引数で一工夫します。
CHOOSE({1,2}, 検索列, 取得列) を使い、「検索列が1列目、取得列が2列目」の仮想テーブルを作成。
この仮想テーブルに対してVLOOKUPを実行し、左方向検索を実現します。
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G3 に以下の数式を入力します。
=VLOOKUP(F3,CHOOSE({1,2},B2:B16,A2:A16),2,FALSE)
動作確認
最後に、G3セルに「A005」が表示されれば成功です。
解説
仮想テーブルの作成 (CHOOSE関数)
CHOOSE({1,2}, B2:B16, A2:A16) は、配列定数 {1,2}を利用します。
これにより、1番目にB列（商品名）、2番目にA列（商品ID）を持つ仮想テーブルをメモリ上に生成します。
このテーブル内では、商品名が1列目、商品IDが2列目として扱われます。
値の検索 (VLOOKUP関数)
VLOOKUP(F3, …, 2, FALSE) は、F3の値 (“定規“) を検索します。
検索対象は、CHOOSE関数が生成した仮想テーブルの1列目（商品名）です。
完全一致 (FALSE) で検索し、該当行の2列目（商品ID）を返します。
メリット＆デメリット
- メリット
- VLOOKUPの「左端列検索」制限の回避
- INDEX/MATCH を使わない左方向検索の一手法
- デメリット
- CHOOSEとの組み合わせのトリッキーさ、初見での理解の難しさ
方法2：水平検索！？ HLOOKUPとTRANSPOSEの奇策！
禁止関数: XLOOKUP, INDEX, MATCH, VLOOKUP, CHOOSE
VLOOKUPが使えない場合の代替策として、水平検索のHLOOKUPを考えます。
しかし、データは垂直配置。そこで一工夫します。
考えかた
TRANSPOSE関数でデータを行列入れ替え（転置）します。
さらにIF({TRUE,FALSE}, …)で、検索列（商品名）を1行目、取得列（商品ID）を2行目とする仮想テーブルを作成。
この転置された仮想テーブルに対してHLOOKUPを実行します。
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G4 に以下の数式を入力します。
=HLOOKUP(F3,TRANSPOSE(IF({TRUE,FALSE},B2:B16,A2:A16)),2,FALSE)
動作確認
最後に、G4セルに「A005」が表示されれば成功です。
解説
仮想テーブルの作成 (IF関数)
IF({TRUE,FALSE}, B2:B16, A2:A16) は、配列定数を利用し、
B列（商品名）とA列（商品ID）を特定の順序で持つ仮想配列を生成します。
行列の入れ替え (TRANSPOSE関数)
TRANSPOSE(…) は、IFが生成した配列を転置。
これにより、1行目が商品名、2行目が商品IDの横方向テーブルになります。
値の検索 (HLOOKUP関数)
HLOOKUP(F3, …, 2, FALSE) は、F3の値 (“定規”) を、
転置テーブルの1行目（商品名）で検索。
完全一致 (FALSE) で見つかった列の、2行目（商品ID）を返します。
メリット＆デメリット
- メリット
- VLOOKUPが使えない状況での代替の可能性
- TRANSPOSE関数の使い方学習
- デメリット
- 非常に回りくどく、可読性が低い
- IF({TRUE,FALSE}, …)部分の直感的でなさ
- 実用性の低さ
方法3：新時代の本命？ FILTER関数で一発！
禁止関数: XLOOKUP, INDEX, MATCH, VLOOKUP, CHOOSE, HLOOKUP, TRANSPOSE, IF
多くの関数が禁止されましたが、最近のExcelには強力なFILTER関数があります。
考えかた
FILTER関数は、条件に合うデータを配列から抽出します。
「商品名がF3と一致する」という条件で、
「商品ID」列(A2:A16)をフィルタリングすれば、目的の値が得られます。
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G5 に以下の数式を入力します。
=FILTER(A2:A16, B2:B16=F3)
動作確認
最後に、G5セルに「A005」が表示されれば成功です。
解説
条件の作成
B2:B16=F3 は、B列（商品名）がF3と一致するかを判定し、TRUE/FALSE配列を生成します。
データの抽出 (FILTER関数)
FILTER(A2:A16, …) は、A列（商品ID）を、上記のTRUE/FALSE配列でフィルタリング。
条件がTRUEの行に対応するA列の値のみを抽出して返します。（一致が複数あればスピル表示）
メリット＆デメリット
- メリット
- 数式のシンプルさと直感的な分かりやすさ
- 複数該当時のスピル表示（今回は該当1件）
- デメリット
- 新しいExcelバージョン（Excel 2021 / Microsoft 365）が必要
- 他の関数の工夫を忘れがちになる可能性
方法4：古典的テクニック！ LOOKUP関数の裏ワザ？
禁止関数: XLOOKUP, INDEX, MATCH, VLOOKUP, CHOOSE, HLOOKUP, TRANSPOSE, IF, FILTER
FILTERも封印。ここでExcelの初期からあるLOOKUP関数を特殊な方法で使います。
考えかた
LOOKUP(検査値, 検査範囲, 対応範囲)のベクトル形式を利用。
検査値に 2、検査範囲に 1/(B2:B16=F3) を指定。
1/(条件) は、条件一致(TRUE)で 1、不一致(FALSE)で #DIV/0! エラーの配列になります。
LOOKUP関数は、この 1 と #DIV/0! の配列から、検査値 2 以下の最大値、つまり最後の 1 を見つけます。（エラーは無視）
そして、その最後の 1 に対応する位置の 対応範囲（A列の商品ID）の値を返します。
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G6 に以下の数式を入力します。
=LOOKUP(2, 1/(B2:B16=F3), A2:A16)
動作確認
最後に、G6セルに「A005」が表示されれば成功です。
解説
検査範囲の作成
1/(B2:B16=F3) で、条件一致箇所が 1、不一致箇所が #DIV/0! となる配列を作成します。
値の検索 (LOOKUP関数)
LOOKUP(2, …, A2:A16) は、上記配列からエラーを無視し、2 以下の最大値 (最後の 1) を検索。
その位置に対応するA列の値を返します。
メリット＆デメリット
- メリット
- 古いバージョンでも利用可能な古典的テクニック
- LOOKUP関数の特殊な使い方の学習
- デメリット
- 動作原理の直感的な理解の難しさ
- 検索値 2 や 1/条件 記述への前提知識の必要性
- 複数一致時に最後に見つかったデータの値が返る仕様への注意
方法5：文字列操作で強引に！？ TEXTJOINとREPTの連携！
禁止関数: XLOOKUP, INDEX, MATCH, VLOOKUP, CHOOSE, HLOOKUP, TRANSPOSE, IF, FILTER, LOOKUP
LOOKUPも禁止。次は文字列操作系の関数で挑戦です。
考えかた
REPT(文字列, 繰り返し回数)で、繰り返し回数に条件式(B2:B16=F3)を使用。
条件一致(TRUE=1)なら商品IDを1回繰り返し、不一致(FALSE=0)なら空文字列””になる配列を作成。
最後にTEXTJOIN(区切り文字, 空白無視, 文字列配列)で、この配列を連結。
「空白無視」をTRUEにすれば、条件に一致した商品IDのみが結果として得られます。
（一致が一つならそのまま表示）
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G7 に以下の数式を入力します。
=TEXTJOIN(“”, TRUE, REPT(A2:A16, B2:B16=F3))
動作確認
最後に、G7セルに「A005」が表示されれば成功です。
解説
条件に応じた文字列配列の生成 (REPT関数)
REPT(A2:A16, B2:B16=F3) は、条件がTRUEの行は対応するA列の値、FALSEの行は空文字列””となる配列を生成します。
文字列の連結 (TEXTJOIN関数)
TEXTJOIN(“”, TRUE, …) は、上記配列を区切り文字なし(“”)、空白無視(TRUE)で連結。
結果、条件に一致したA列の値のみが残ります。
メリット＆デメリット
- メリット
- 検索・抽出系関数が使えない場合の文字列操作による代替発想
- REPTと条件式、TEXTJOINの空白無視オプションの組み合わせ学習
- デメリット
- 処理内容のトリッキーさと分かりにくさ
- 複数一致時に結果が連結されて返る仕様への注意
- TEXTJOINが比較的新しい関数である点
方法6：位置特定からのオフセット！ OFFSET, AGGREGATE, ROWの合わせ技！
禁止関数: XLOOKUP, INDEX, MATCH, VLOOKUP, CHOOSE, HLOOKUP, TRANSPOSE, IF, FILTER, LOOKUP, TEXTJOIN, REPT
文字列操作も禁止。
次は、条件に合う行の位置を特定し、OFFSETで値を取得します。
そろそろ辛くなってきました。
考えかた
まず、条件(B2:B16=F3)に合う行の行番号を特定します。
ROW(B2:B16) で行番号配列を取得し、条件 (B2:B16=F3) で割ります。
（一致行は行番号、不一致行は#DIV/0!）
次にAGGREGATE(15, 6, …, 1)を使用。
オプション15(SMALL相当)と6(エラー無視)で、条件一致した行番号の中から最小値を取得。
最後にOFFSET(基準セル, 行数, 列数)で、基準セルA1から、AGGREGATEで求めた行番号、0列シフトしたセルの値を取得します。
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G8 に以下の数式を入力します。
=OFFSET(A1, AGGREGATE(15, 6, ROW(B1:B15)/(B2:B16=F3), 1) , 0)
動作確認
最後に、G8セルに「A005」が表示されれば成功です。
解説
条件一致行の相対位置特定 (ROW, AGGREGATE関数)
ROW(B1:B15) で、{1, 2, …, 15} の連番配列を生成します。
ROW(B1:B15)/(B2:B16=F3) で、条件一致行はその連番（範囲内の相対位置）、不一致行は#DIV/0!となる配列を作成。
AGGREGATE(15, 6, …, 1) で、エラーを無視し最小の連番（”定規” は範囲内で5番目なので「5」）を取得します。
値の取得 (OFFSET関数)
OFFSET(A1, …, 0) で、基準セル A1 から、AGGREGATEが返した値（5）行下、0 列右のセル、
つまりサンプルデータでは A6 セル（A005が入力されているセル）の値を参照します。
メリット＆デメリット
- メリット
- AGGREGATEのエラー無視オプション等の高度なテクニック学習
- 検索手段が限られた場合の揮発性関数(OFFSET)利用の選択肢
- デメリット
- 数式の複雑さ、分かりにくさ
- AGGREGATE自体の理解の難しさ
- OFFSET(揮発性関数)によるブック再計算速度への影響可能性
方法7：最終手段！？ INDIRECTとADDRESSでセルを狙い撃ち！
禁止関数: XLOOKUP, INDEX, MATCH, VLOOKUP, CHOOSE, HLOOKUP, TRANSPOSE, IF, FILTER, LOOKUP, TEXTJOIN, REPT, OFFSET, AGGREGATE, ROW
ついに最終手段。条件に合うセルのアドレス文字列を生成し、INDIRECTで参照します。
考えかた
まず、条件 (B2:B16=F3) に一致する行の行番号（データ範囲の開始行を考慮）を求めます。
SEQUENCE(COUNTA(B2:B16), 1, 2) で連番 {2, …, 16} を生成。
これと条件 (B2:B16=F3) を掛け合わせ（一致行は行番号、不一致行は0）、
SUMPRODUCTで合計し、目的の行番号を取得（複数一致非対応）。
次にADDRESS(行番号, 列番号)で、
求めた行番号と列番号 1 (A列) からセルアドレス文字列 ($A$6) を生成。
最後にINDIRECT(アドレス文字列)で、そのセル ($A$6) の値を参照します。
手順
検索条件の入力
まず、F3セルに「定規」が入力されていることを確認します。
数式の入力
次に、結果を表示したいセル G9 に以下の数式を入力します。
=INDIRECT(ADDRESS(SUMPRODUCT((B2:B16=F3)*SEQUENCE(COUNTA(B2:B16), 1, 2)), 1))
動作確認
最後に、G9セルに「A005」が表示されれば成功です。
解説
条件一致行の行番号特定 (SEQUENCE, SUMPRODUCT関数)
(B2:B16=F3)*SEQUENCE(COUNTA(B2:B16), 1, 2) で、一致行の行番号（他は0）の配列を作成。
SUMPRODUCT(…) でその合計（今回は 6）を取得します。（※単一一致前提）
セルアドレスの生成 (ADDRESS関数)
ADDRESS(…, 1) で、行番号 6、列番号 1 からアドレス文字列 $A$6 を生成します。
値の取得 (INDIRECT関数)
INDIRECT(…) で、文字列 $A$6 が示すセル（A6）の値を返します。
メリット＆デメリット
- メリット
- 多数関数禁止下でも値取得が可能であることの証明
- ADDRESS, INDIRECT等の組み合わせ体験
- 最後の手段としての知識
- デメリット
- 超絶的な分かりにくさ、解読困難
- INDIRECT(揮発性関数)によるブック再計算への影響可能性
- 実用性は皆無、完全な自己満足領域
今回使用した関数一覧
今回の記事で登場した主な関数です。
- VLOOKUP: 垂直検索
- CHOOSE: 値の選択、配列との組み合わせで範囲作成
- HLOOKUP: 水平検索
- TRANSPOSE: 行列入れ替え
- IF: 条件分岐、配列生成
- FILTER: 条件に合うデータを抽出
- LOOKUP: 特定形式で、条件に合う最後の値検索（裏ワザ）
- TEXTJOIN: 文字列連結（空白無視可）
- REPT: 文字列繰り返し
- OFFSET: 相対位置のセル参照（揮発性）
- AGGREGATE: 集計（エラー無視等オプション豊富）
- ROW: 行番号取得
- INDIRECT: 文字列によるセル参照（揮発性）
- ADDRESS: セルアドレス文字列生成
- SUMPRODUCT: 配列要素の積和、条件処理に応用
- SEQUENCE: 連番配列生成
- COUNTA:空白でないセル数カウント
- (禁止関数): XLOOKUP, INDEX, MATCH
まとめ
便利な関数を封印し、制限を加えながら値を取得する多様な方法を見てきました。
VLOOKUPの工夫から、FILTERの明快さ、難解な力業まで様々でした。
後半の方法は実用的ではありませんが、重要なのは試行錯誤のプロセスです。
「こんな使い方もできるのか！」という発見が、スキル向上の糧となります。
一つの正解だけでなく、多様な選択肢とロジックを知ることが応用力に繋がります。
このパズルが、皆さんのExcelの世界を広げる一助となれば幸いです。
