Excelで表から商品名、単価、担当者名などを取り出したいとき、LOOKUP関数は知っておくと便利な検索関数です。
ただし、LOOKUP関数は検索範囲の並び順や近似一致の仕様に注意が必要であり、正確に使い分けなければ意図しないデータを返すことがあります。
この記事では、検索値を基準にしたデータ取得、ベクトル形式と配列形式の違い、複数条件に対応させる考え方まで、実務で使える形で解説します。
LOOKUP関数で押さえたい要点
・検索範囲は原則として昇順に並べる
・完全一致ではなく、検索値以下の最大値を探す
・別列の対応データを返せる
・複数条件は検索キーを組み合わせて扱う
関数の仕組みを理解すると、売上表、在庫表、等級判定表などのデータ処理を効率化できます。
LOOKUP関数による検索値からのデータ取得
それではまず、LOOKUP関数で検索値に対応するデータを取得する基本方法について解説していきます。
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A001 | ノート | 120 |
| A002 | ボールペン | 150 |
| A003 | ファイル | 280 |
| A004 | 電卓 | 1680 |
この表では、A列に商品コード、B列に商品名、C列に単価を入力し、1行目は見出しとして扱います。
ベクトル形式の基本構文
LOOKUP関数の基本となるベクトル形式は、検索値、検索範囲、結果範囲の順に指定します。
=LOOKUP(検索値,検索範囲,結果範囲)
たとえば、E2セルに商品コードを入力し、F2セルに商品名を表示する場合は、=LOOKUP(E2,A2:A5,B2:B5)と入力します。
この数式は、E2の商品コードをA2からA5で探し、同じ行にあるB列の商品名を返す仕組みです。
結果範囲には、検索範囲と同じ件数のセル範囲を指定する必要があります。
検索範囲が4行なのに結果範囲を3行にすると、対応関係が崩れるため正しい結果を得られません。
なお、LOOKUP関数は文字列だけでなく数値、日付、時刻も検索値として使用できます。
【操作のポイント】検索範囲と結果範囲は、開始行と終了行をそろえて指定します。
検索値を入力するセルの準備
検索値を数式の中へ直接書くこともできますが、実務では検索専用のセルを設ける方法が便利です。
たとえばE1セルに「検索商品コード」、F1セルに「取得結果」と見出しを作り、E2にA003と入力します。
F2には=LOOKUP(E2,$A$2:$A$5,$B$2:$B$5)を設定すると、E2の内容を変更するだけで商品名を検索できます。
ドル記号を付けた絶対参照にしておけば、数式を下方向へコピーするときも、商品一覧の範囲がずれません。
検索値が入力されていない場合には、LOOKUP関数が予想外の値を返すことがあります。
空欄時は何も表示しないようにしたい場合、=IF(E2=””,””,LOOKUP(E2,$A$2:$A$5,$B$2:$B$5))のようにIF関数と組み合わせましょう。
入力セルを目立つ色で塗りつぶし、結果セルと区別すると、利用者にも操作箇所が伝わりやすくなります。
【操作のポイント】検索値を入力するセルと、関数を入力するセルは分けて作成します。
別列のデータを返す指定
LOOKUP関数では、同じ検索値を使って商品名だけでなく単価や分類なども取り出せます。
単価を返したい場合は、結果範囲だけをC2:C5へ変更し、=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)とします。
検索範囲は商品コードの列で固定し、何を表示するかによって結果範囲を切り替える考え方です。
G2に商品名、H2に単価を表示したいなら、G2とH2にそれぞれ別のLOOKUP関数を設定できます。
同じ商品コードから複数の情報を取得できるため、見積書、発注書、商品検索シートなどで役立つ構成です。
ただし、検索範囲より左側にある列の値は、通常のLOOKUP関数では扱いにくくなります。
左右どちらの列も柔軟に検索したい場合は、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせも検討しましょう。
【操作のポイント】返したい項目ごとに、結果範囲だけを変更して数式を作成します。
LOOKUP関数の近似一致と昇順の並び替え
続いては、LOOKUP関数で特に重要な近似一致とデータの並び順を確認していきます。
| 下限点 | 評価 | 支給率 |
|---|---|---|
| 0 | D | 0% |
| 60 | C | 50% |
| 70 | B | 80% |
| 80 | A | 100% |
この表では、A列の下限点を検索し、得点に応じた評価や支給率を取得できます。
検索値以下の最大値を返す仕様
LOOKUP関数は、検索値と完全に同じ値がない場合でも、検索値以下で最も大きい値を見つけて結果を返します。
たとえばE2セルに76を入力し、=LOOKUP(E2,A2:A5,B2:B5)と設定すると、70に対応するBが返ります。
76という値は表にはありませんが、76以下の数値のうち最大なのは70だからです。
得点が76点の場合
0、60、70、80のうち、76以下で最も大きい値は70
したがって、70点の区分であるBを返す仕組みです。
この性質は、成績評価、所得税率、送料区分、数量割引、勤続年数に応じた手当などに適しています。
反対に、社員番号や伝票番号のように完全に一致しなければ困るデータには、そのまま使わないほうが安全です。
【操作のポイント】LOOKUP関数は一致しない値でも近い下限の区分を返す関数です。
検索範囲を昇順にする理由
LOOKUP関数を正しく使うためには、検索範囲を小さい順、または文字の並び順に整える必要があります。
先ほどの評価表なら、0、60、70、80の順に並んでいる状態が必要です。
もし70、0、80、60のようにランダムな順序で並べると、検索結果が誤る可能性があります。
LOOKUP関数は検索範囲が昇順であることを前提に動作するため、見た目では正しそうでも計算結果が信頼できなくなります。
数値の並べ替えは、表内のセルを選択し、データタブから昇順を選ぶことで実行できます。
商品コードのような文字列を検索する場合も、コードの並びが昇順かどうかを確認しましょう。
先頭のゼロを含むコードは文字列として扱われることがあるため、検索列のデータ形式も統一することが大切です。
【操作のポイント】近似一致で使う検索列は、数式を入れる前に必ず昇順へ並べ替えます。
完全一致を求める場合の注意点
LOOKUP関数には、VLOOKUP関数のような完全一致を指定する引数がありません。
そのため、A005を検索してもA004までしか登録されていない場合、A004の結果を返すことがあります。
商品コードの検索でこれが起こると、存在しないコードなのに別の商品を表示するため注意が必要です。
完全一致が必要なケースでは、XLOOKUP関数の一致モードを完全一致にする方法、またはVLOOKUP関数の第4引数をFALSEにする方法が向いています。
ExcelのバージョンによってXLOOKUP関数を利用できない場合には、VLOOKUP関数やINDEX関数とMATCH関数の組み合わせを選びましょう。
一方、区分表のように下限値から判定したい場面では、LOOKUP関数の近似一致がむしろ大きな利点になります。
関数を選ぶ前に、探したい値が完全一致か、範囲に応じた判定かを整理することが重要です。
【操作のポイント】コード検索には完全一致向きの関数、区分判定にはLOOKUP関数を使い分けます。
配列形式による行列データの検索
続いては、LOOKUP関数の配列形式と、表の最後の行または列を使う検索方法を確認していきます。
| 月 | 売上 | 粗利 |
|---|---|---|
| 4月 | 420000 | 126000 |
| 5月 | 385000 | 115500 |
| 6月 | 510000 | 153000 |
| 7月 | 470000 | 141000 |
配列形式は、縦または横に並んだデータを対象に、最後の行や列から対応値を取得するときに利用できます。
配列形式の構文
LOOKUP関数の配列形式は、検索値と配列を指定する書き方です。
=LOOKUP(検索値,配列)
配列が縦長の表である場合、LOOKUP関数は先頭列を検索し、最後の列から結果を返します。
横長の表である場合は、先頭行を検索し、最後の行から結果を返す仕様です。
ただし、どの列や行を返すかを細かく指定できないため、通常のデータ検索ではベクトル形式のほうがわかりやすいでしょう。
配列形式は、表の構造を理解している場合に限定して使うと、数式の管理ミスを減らせます。
【操作のポイント】返す列を明確に指定したいときは、基本的にベクトル形式を選びます。
最終入力セルを取得する応用
LOOKUP関数の代表的な応用に、範囲内で最後に入力された値を取り出す方法があります。
たとえばB2:B100に売上が入力され、途中に空白行が混ざる場合でも、最終入力値を求められます。
=LOOKUP(2,1/(B2:B100<>””),B2:B100)
この式では、B列が空白ではないセルに対して1を作り、空白セルではエラーを発生させます。
検索値の2は範囲に存在しないため、LOOKUP関数は1のうち最後にある値を探し、その位置のB列データを返します。
日報、ログ、入力履歴などで最新の数値を表示したいときに便利な数式です。
ただし、数式の結果が空文字列になっているセルは、見た目が空欄でも空白ではないと判定される場合があります。
表の作り方によって結果が変わるため、実際のデータで検証してください。
【操作のポイント】最終値の取得では、検索範囲と結果範囲を同じ列にそろえます。
エラーと空白の扱い
LOOKUP関数は、検索範囲にエラー値が含まれる場合や、検索値が先頭の値より小さい場合にエラーを返すことがあります。
たとえば評価表が60点から始まっている状態で50点を検索すると、該当する下限値がないためエラーになる可能性があります。
このようなときは、IFERROR関数で数式を囲むと、表示を整えられます。
=IFERROR(LOOKUP(E2,A2:A5,B2:B5),”対象外”)
エラーをすべて隠すだけでは、入力ミスに気づけなくなることもあります。
業務表では「対象外」「コードを確認してください」など、利用者が原因を判断できる表示を設定すると実用的です。
エラー処理は見栄えのためだけではなく、入力品質を保つための仕組みでもあります。
【操作のポイント】エラーを隠す場合は、利用者に必要な案内文を返すように設定します。
複数条件を組み合わせた検索キー
続いては、LOOKUP関数で複数条件を扱うための検索キーの作り方を確認していきます。
| 支店 | 商品 | 担当者 | 在庫数 |
|---|---|---|---|
| 東京 | ノート | 田中 | 120 |
| 大阪 | ノート | 佐藤 | 85 |
| 東京 | 電卓 | 鈴木 | 34 |
| 大阪 | 電卓 | 高橋 | 42 |
支店と商品のように、ひとつの条件だけでは行を特定できない場合、条件を結合した検索値を作る方法があります。
条件を文字列として結合する方法
複数条件検索の基本は、支店と商品を結合して、ひとつの検索キーにする考え方です。
検索用セルがF2に支店、G2に商品として入力されている場合、検索値はF2&G2で作成できます。
検索範囲側でもA2:A5&B2:B5のように、支店列と商品列を結合します。
=LOOKUP(F2&G2,A2:A5&B2:B5,D2:D5)
ただし、このままではLOOKUP関数の近似一致と並び順の影響を受けるため、完全に行を特定する用途では注意が必要です。
文字列を結合する際には、境界が曖昧にならないよう区切り文字を入れる工夫も有効です。
たとえば東京とノートは、A2&”|”&B2のように連結できます。
【操作のポイント】複数条件は、各条件を同じ順番で結合して一つの検索キーにします。
条件ごとの重複確認
複数条件を設定しても、その組み合わせ自体が重複していれば、どの行を返すべきか判断できません。
たとえば東京、ノートという条件の行が表に2行ある場合、在庫数が複数存在することになります。
検索前に、条件の組み合わせで対象行が一意に決まるか確認することが重要です。
補助列を作り、=A2&”|”&B2のような結合キーを入力すると、重複を確認しやすくなります。
さらにCOUNTIF関数を使えば、同じキーが複数回ある行を検出できます。
=COUNTIF($E$2:$E$100,E2)
結果が2以上なら、その結合キーは重複しています。
担当者名、日付、伝票番号など、行を特定できる条件を追加することも検討しましょう。
【操作のポイント】検索式を作る前に、複数条件の組み合わせが重複していないかを確認します。
複数条件検索に適した関数の選択
LOOKUP関数でも条件の組み合わせは扱えますが、完全一致の複数条件検索にはXLOOKUP関数やFILTER関数が適した場面があります。
Microsoft 365やExcel 2021以降を利用できるなら、XLOOKUP関数で結合キーを完全一致検索する方法がわかりやすいでしょう。
複数の該当データを一覧で取得したい場合は、FILTER関数が有力な選択肢です。
一方で、得点区分や数量区分のように下限値を基準に判定する処理では、LOOKUP関数が簡潔です。
関数の名前ではなく、完全一致か近似一致か、結果が一件か複数件かで選ぶと判断しやすくなります。
古いバージョンのExcelとファイルを共有する環境では、利用できる関数の種類もあらかじめ確認してください。
【操作のポイント】複数条件で正確な一致が必要なら、LOOKUP関数以外も比較します。
LOOKUP関数と関連検索関数の使い分け
続いては、LOOKUP関数とVLOOKUP関数、XLOOKUP関数などの違いを確認していきます。
| 関数 | 主な特徴 | 向く場面 |
|---|---|---|
| LOOKUP | 近似一致、簡潔な区分判定 | 評価表、料金表 |
| VLOOKUP | 左端列を検索 | 縦長のマスタ表 |
| XLOOKUP | 完全一致、左右検索 | 柔軟なデータ検索 |
| INDEXとMATCH | 旧版でも柔軟 | 複雑な検索 |
VLOOKUP関数との違い
VLOOKUP関数は、表の左端列を検索し、指定した列番号のデータを返す関数です。
完全一致を指定できるため、商品コードや顧客番号の検索ではLOOKUP関数より安全に使えることがあります。
ただし、返す列は検索列より右側である必要があり、列を追加すると列番号の修正が必要になる場合があります。
LOOKUP関数は検索範囲と結果範囲を別々に指定できるため、式の構造が比較的わかりやすい面もあります。
区分表の下限値検索ならLOOKUP関数、コードの完全一致検索ならVLOOKUP関数という整理が基本です。
【操作のポイント】VLOOKUP関数は完全一致の指定を忘れず、FALSEまたは0を設定します。
XLOOKUP関数との違い
XLOOKUP関数は、比較的新しいExcelで使える検索関数です。
検索範囲と戻り範囲を別々に指定でき、左方向への検索にも対応し、見つからない場合の表示も設定できます。
完全一致が既定であるため、マスタデータからの正確な検索に向いています。
一方、LOOKUP関数のように検索値以下の最大値を返す近似一致も、設定によって利用できます。
=XLOOKUP(E2,A2:A5,B2:B5,”見つかりません”,-1)
一致モードに-1を指定すると、完全一致または次に小さい値を検索します。
新しい環境だけで利用するファイルなら、XLOOKUP関数へ移行すると保守しやすくなるケースもあります。
【操作のポイント】共有先のExcelバージョンを確認してからXLOOKUP関数を採用します。
INDEX関数とMATCH関数の組み合わせ
INDEX関数とMATCH関数を組み合わせると、列番号に依存せず、柔軟な検索式を作成できます。
MATCH関数で検索値の位置を求め、INDEX関数でその位置にある結果を返す仕組みです。
完全一致の例では、=INDEX(B2:B5,MATCH(E2,A2:A5,0))のように入力します。
MATCH関数の最後の0は完全一致を表し、検索値がない場合に別データを誤表示しにくい点が利点です。
数式はLOOKUP関数より長くなりますが、データの列構成が変わる可能性がある表では有効です。
複数条件や二次元検索へ発展させる場合にも、INDEX関数とMATCH関数の知識が役立ちます。
【操作のポイント】列の追加や並べ替えが多い表では、INDEX関数とMATCH関数も候補にします。
まとめ LOOKUP関数の使い方とデータ取得
ExcelのLOOKUP関数は、検索値に対応するデータを取り出すだけでなく、得点や金額に応じた区分を判定する場面で活用できる関数です。
ベクトル形式では、検索値、検索範囲、結果範囲を指定し、商品名や単価など別列の情報を取得できます。
検索範囲を昇順に並べることは、LOOKUP関数を正しく使うための最重要ポイントです。
完全一致ではなく検索値以下の最大値を返す特性を理解すれば、評価表、割引表、料金表などで効率よく利用できます。
複数条件では条件を結合した検索キーを使えますが、正確な一致が必要な場合にはXLOOKUP関数、VLOOKUP関数、INDEX関数とMATCH関数も使い分けましょう。
まずは小さなサンプル表で数式を試し、検索範囲の並び順と返される結果を確認しながら、実際の業務シートへ取り入れていきましょう。
