XLOOKUPとVLOOKUPは何が違うのか

LWP | XLOOKUPとVLOOKUPは何が違うのか

図解1｜引数を並べると、設計の違いが見える

同じ検索結果でも、関数へ渡す情報の形は異なります。引数の名前と役割から、その違いを読み解きます。

解説1｜「何列目」と「何を返すか」を区別する

ストーリー

シャケモンとアユさんは、商品コードから単価を返す数式を見比べています。シャケモンはExcelの具体例を、アユさんは引数同士の受け渡しを持ち寄ります。結果だけを比べると同じでも、「表の中の位置」を指定するか、「返すデータの範囲」を指定するかで、式の組み立て方が変わります。

VLOOKUPは、検索列と戻す列を含む一つの表を受け取ります。検索対象はその表の左端列です。戻す項目は、表の左から数えた列番号で指定します。XLOOKUPは、探す範囲と返す範囲を分けて受け取り、検索範囲で一致した位置に対応する値を戻り配列から返します。

関数の役割

役割	VLOOKUP	XLOOKUP
検索値	lookup_value	lookup_value
検索対象	table_arrayの左端列	lookup_array
返す対象	col_index_num（位置番号）	return_array（範囲・配列）
未一致時	外側でIFNAなどを使う	if_not_found
一致方法	range_lookup	match_mode
検索方法・方向	独立した引数はない	search_mode

XLOOKUPのreturn_arrayは「列名」という文字列ではありません。実際に返すデータの範囲や配列です。Excelテーブルを使うと、その列の範囲を「商品表[単価]」のように列名を含む構造化参照で表せます。セル番地で指定する場合も、受け渡す役割は同じです。

読むポイント

VLOOKUPの第4引数、XLOOKUPの第4〜第6引数は省略可能です。ただし、省略したときの一致方法は同じではありません。図の引数一覧は役割の比較です。例を自分の表へ移すときは、戻す対象に加え、一致条件と未一致時の扱いも確認します。

この説明は関数の公開された引数と結果の構造を扱います。内部実装の同一性や、どちらが常に高速かを示すものではありません。

図解2｜同じ200を、違う指定で取り出す

一行は一つの商品です。同じ入力を固定し、表の3列目と単価列の直接参照を比較します。

解説2｜一つの商品表で引数を具体化する

入力例：基本シート A1:D4

行	A列	B列	C列	D列
1	商品コード	商品名	単価	在庫数
2	A001	商品A	100	20
3	A002	商品B	200	15
4	A003	商品C	300	10

一行が一つの商品、A列が検索する商品コード、C列が返したい単価です。F2へ「A002」を入力します。比較する式は同じシートのG2とG3へ置き、同じ商品・同じ項目が返ることを確かめます。

入力する数式：G2（VLOOKUP）

=VLOOKUP(F2,A2:D4,3,FALSE)

A2:D4は、見出しを除いた商品表です。左端のA列からF2の値を探し、一致した行の3列目を返します。ここではA列を1列目と数えるため、3列目はC列です。シート全体の列番号を渡すのではなく、指定した表の中で数えます。FALSEは完全一致の指定です。

入力する数式：G3（XLOOKUP）

=XLOOKUP(F2,A2:A4,C2:C4)

第2引数が探す範囲、第3引数が返す範囲です。A2:A4でA002が見つかるのは範囲内の2番目なので、C2:C4の2番目であるC3を返します。一致方法を省略したこの式は、既定の完全一致で動きます。

計算結果と確認

G2もG3も200です。A002があるシートの3行目と、C3の単価200を直接照合します。F2をA003に変えると300になります。結果が出たことだけでなく、どの商品・どの列に対応した結果かを確認すると、列番号の数え間違いを見つけやすくなります。

検索範囲と戻り範囲は、同じ商品が同じ位置に並ぶ必要があります。XLOOKUPで片方だけ一行ずらすと、長さが同じでも別の商品に対応します。長さや並びを確認する理由は、この位置対応にあります。

図解3｜列の番号と、列の参照は違う

固定の列番号と列そのものの参照は、表の構造が変わったときの意味も異なります。

解説3｜テーブル名で読みやすくし、列変更を考える

入力例：テーブルにした商品表

前ページのA1:D4を、見出し付きのExcelテーブルへ変換し、テーブル名を「商品表」にします。この例では別シート「テーブル」で同じ商品データを使い、F2へA002を入力します。一般のセル範囲に名前を付けることと、Excelテーブルを作ることは別の操作です。

入力する数式：G2・G3

=VLOOKUP(F2,商品表,3,FALSE)

=XLOOKUP(F2,商品表[商品コード],商品表[単価])

どちらも200です。VLOOKUPはテーブルを渡しても列番号を使います。XLOOKUPでは「商品コード列から探し、単価列を返す」という対応を式から読めます。商品表[単価]は、見出しを除いた単価列のデータを指します。検索値を別のテーブルの現在行から取る場合は、F2に代えて[@商品コード]などの行参照を使えます。

左側の列を返す：基本シート G4

=XLOOKUP(F4,B2:B4,A2:A4)

F4へ「商品B」を入れるとA002が返ります。B列を探し、左側のA列を戻り範囲に指定できるためです。VLOOKUPは、指定した表の左端列で探します。元の並びのままB列を検索列、A列を戻り列にする指定はできません。表を組み替える等の別の工夫と、関数単体の引数を区別します。

読むポイント：途中に列を挿入した場合

元のA〜D列の表へ、単価の手前に「分類」を一列挿入すると、3列目は分類、単価は4列目になります。VLOOKUPの引数へ書いた固定値3は、そのまま3なので、分類の値を返してしまいます。通常の列挿入でC列の単価がD列へ移ると、XLOOKUPのセル参照はその移動に合わせて調整されます。

これは「XLOOKUPなら壊れない」という意味ではありません。参照する列そのものの削除、検索範囲と戻り範囲のずれ、別データへの差し替えは確認が必要です。変更後の列名・式・期待結果を一組で見直します。

図解4｜MATCHの位置番号を、VLOOKUPへ渡す

商品コードで行を選び、項目名で列を選ぶと、その交点の値を取得できます。

解説4｜見出しと表の開始列をそろえる

入力例：二次元Vシート B9:G14

行	B列	C列	D列	E列	F列	G列
9	商品コード	商品名	単価	在庫数	原価	利益
10	A001	商品A	100	20	80	20
11	A002	商品B	200	15	150	50
12	A003	商品C	300	10	240	60
13	A004	商品D	400	5	300	100
14	A005	商品E	500	8	400	100

一行は一つの商品です。見出しを含む範囲はB9:G14、データだけならB10:G14です。I10へA002、I11へA003、J9へ単価、K9へ在庫数を入力します。結果をJ10から右・下へ展開する配置です。

途中の計算：MATCHが返す3

=MATCH(J$9,$B$9:$G$9,0)

MATCHは値そのものではなく、検索範囲内の相対位置を返します。B9から数えると、商品コードが1、商品名が2、単価が3なので結果は3です。第3引数の0は完全一致です。省略すると既定の近似一致になるため、見出し名を探すこの例では0を明示します。

入力する数式：J10

=VLOOKUP($I10,$B$10:$G$14,MATCH(J$9,$B$9:$G$9,0),0)

MATCHの3がVLOOKUPの戻り列番号へ入り、A002の行と単価の列の交点200が返ります。最後の0はVLOOKUPの完全一致です。MATCHの0と役割は似ていますが、別の関数へ渡している引数です。

コピー後の結果と固定する軸

J10をK10へコピーすると15、J11へコピーすると300です。$I10は商品コードのI列だけ、J$9は項目名の9行目だけを固定します。表と見出し範囲は全体を固定します。MATCHの範囲をC9:G9から始めると単価は2になり、B列開始のVLOOKUPへ渡す番号とずれます。両方を同じB列から始めることが、この組み合わせの条件です。

図解5｜二重XLOOKUPは、配列を受け渡す

内側が戻す列のデータを選び、外側がそのデータから商品コードに対応する値を返します。

解説5｜内側の結果を具体的な五つの値で読む

入力例：二次元Xシート

前ページと同じB9:G14の商品表を、別シート「二次元X」へ置きます。I10=A002、I11=A003、J9=単価、K9=在庫数も同じです。XLOOKUPには「戻り列番号」という引数がないため、MATCHの位置番号3をそのまま第3引数へ渡す方法にはなりません。第3引数が必要とするのは、返すデータの配列です。

途中の計算：内側が選ぶ単価列

=XLOOKUP(J$9,$B$9:$G$9,$B$10:$G$14)

内側は横一列の見出しB9:G9から単価を探します。戻り配列B10:G14では、対応する単価の列を、5行分まとめて返します。戻り値は100・200・300・400・500という縦方向の配列です。この式だけをH17へ入力すると、H17:H21へ五つの値がスピルします。試す際はその出力先を空けておきます。

入力する数式：J10（完成形）

=XLOOKUP($I10,$B$10:$B$14,XLOOKUP(J$9,$B$9:$G$9,$B$10:$G$14))

外側はB10:B14の商品コードからA002を探します。A002はその範囲の2番目なので、内側から渡された単価配列の2番目、200を返します。完成形では内側の配列を外側が利用し、J10へは200が出ます。五つの単価を、完成形の出力セルへ別途展開する式ではありません。

計算結果と読むポイント

J10をK10へコピーすると、内側が在庫数の列を選び、結果は15になります。J11へコピーすればA003の単価300です。前ページと同じ固定参照が使えます。見出しが重複していないこと、検索コードと戻る配列の行順がそろうことも条件です。

VLOOKUP＋MATCHでは「列番号」を渡し、二重XLOOKUPでは「選んだ列の配列」を渡します。どちらも行と列の交点を得ますが、組み合わせる関数が受け取るものは異なります。INDEX等を使う別の組み方もあります。ここでは二つの例を比較し、二重XLOOKUPだけが唯一の方法とはしません。

図解6｜未一致と、式の不具合を分ける

「商品が存在しない」と「式が表の範囲を外れている」は、異なる確認が必要な状態です。

解説6｜代替値を出す前に、何を扱うかを決める

入力例：基本シートのA999

基本シートA2:D4にはA001〜A003しかありません。A999を完全一致で探すと、どちらの検索関数も、未一致時の指定がなければ#N/Aになります。商品が登録されていない場合を、読み手へ分かる表示にする二つの式を比べます。

入力する数式：基本シート G5・G6

=IFNA(VLOOKUP("A999",A2:D4,3,FALSE),"該当なし")

=XLOOKUP("A999",A2:A4,C2:C4,"該当なし")

どちらも「該当なし」が返ります。IFNAは第1引数の式全体が#N/Aなら第2引数を返す関数です。VLOOKUP自身に未一致時の引数がないため、外側で結果を扱います。XLOOKUPでは第4引数へ代替値を渡せます。

関数の役割と確認する違い

方法	対応する状況	確認する点
IFNA	式全体の#N/A	未登録以外の#N/Aも対象になる
IFERROR	#N/A・#REF!・#VALUE!等	式の不具合も代替表示に変わり得る
XLOOKUP第4引数	有効な一致がない	参照や配列の不具合すべてを扱う指定ではない

範囲の不具合を区別する

A2:D4は4列です。戻り列番号を5へ変えるとVLOOKUPは#REF!になります。図のA001の例も同じです。基本シートのF2=A002を使った次の式でも#REF!が残ります。

=IFNA(VLOOKUP(F2,A2:D4,5,FALSE),"該当なし")

IFERRORで包んで「式を確認」などを指定すると、その#REF!も代替値へ変わります。エラー処理は不具合そのものを修復する操作ではありません。空文字列で隠す前に、検索値・参照先・列番号を確認します。XLOOKUPも、A2:A4に対してC2:C3を返すような長さの不一致は、通常#VALUE!となり、第4引数だけでは解消しません。

図解7｜一致方法と、検索方向は別の指定

何を一致とみなすかと、どちら側から探すかを分けます。末尾から探す指定は、近似一致の指定とは別です。

解説7｜省略値・重複・並べ替えの条件を読む

入力例：重複シート A1:B4

行	A列	B列
1	商品コード	単価
2	A001	100
3	A002	200
4	A002	250

F2へA002を入力します。これは同じコードを二つ含む確認用の表で、基本シートの商品マスターとは別です。既定のXLOOKUPは先頭から探すので200、検索モード-1は末尾から探すので250を返します。

入力する数式：G3・G4

=XLOOKUP(F2,A2:A4,B2:B4)

=XLOOKUP(F2,A2:A4,B2:B4,"該当なし",0,-1)

第5引数の0は完全一致、第6引数の-1は末尾からの検索です。途中の引数を飛ばして、-1を違う位置へ入れないようにします。VLOOKUPの完全一致も、重複があると最初に見つかった一致を使います。

一致方法：省略した結果をそろえない

VLOOKUPの第4引数を省略するとTRUEの近似一致です。左端列の昇順整列を前提とし、検索値に一致する値、なければ検索値以下で最大のキーに対応する戻り列の値を返します。キーそのものを返すとは限りません。商品コードのマスター照合ではFALSE／0を明示します。MATCHも見出し照合では0を明示します。

XLOOKUPの一致モードは0が既定です。-1は完全一致、なければ次に小さいキー、1は次に大きいキーに対応する値を返します。2はワイルドカード一致で、?は任意の1文字、*は任意の文字列、~はこれらを文字として探すために使います。VLOOKUPやMATCHも、完全一致指定で文字列を探すときはワイルドカードに注意します。

読むポイント：検索モードと運用上の順序

XLOOKUPの検索モード2は検索範囲の昇順、-2は降順を前提とする二分検索です。指定どおりに整列されていないと正しくない結果になり得ます。一致モードの-1と、検索モードの-1は別の指定です。また、末尾から返した250を「最新単価」と呼べるかは、日付や並べ替えの運用次第です。行の末尾だけで新しさを決めません。

図解8｜選ぶ基準は、戻す対象と利用環境

単純な検索、見出しを使う検索、共有相手のExcelを分けて考えます。

解説8｜引数を読むことが、保守する力になる

まとめ

一つの列から探し、決まった別の列を返すなら、XLOOKUPの検索範囲と戻り配列は目的を直接読み取りやすい指定です。左側へ返す場合も同じ構造で考えられます。一方、VLOOKUPの戻り列番号は数値を受け取るため、MATCHが返す位置番号を接続できます。「戻す項目名を変える」場面で、このつながりを理解しておくと、既存の式を読み解けます。

二重XLOOKUPでは、内側が項目名に対応する列のデータを返し、外側が商品コードに対応する行を選びます。VLOOKUPを短く書き直す発想だけでなく、関数へ何を渡すかを組み替える例として読めます。どの式も、検索キー・見出し・範囲の対応が成立することを確認して使います。

利用環境と、置き換えを判断する材料

XLOOKUPはMicrosoft 365、Excel 2021・2024等で利用できます。Excel 2016・2019では使用できません。共有相手がどの環境で編集・再計算するかを確認します。既存のVLOOKUPを残すか改めるかは、利用環境、説明のしやすさ、表の変更、既存の照合結果を踏まえて判断します。新しい関数があることだけで、確認済みの資産を一括で置き換える必要はありません。

今回の仕掛け

同じ商品表・同じ検索値・同じ期待値を固定し、戻す対象だけを見比べました。位置番号3と単価の5行配列は、同じ役割を果たすために渡されても、同じデータではありません。引数と戻り値の対応を読むと、MATCHとの接続、二重検索、未一致処理が一つの筋でつながります。

出典・確認範囲

Microsoft公式のXLOOKUP関数、VLOOKUP関数、MATCH関数、IFNA関数、IFERROR関数を2026年10月11日に確認しました。元ネタの論旨を基に、範囲・数値・コピー後の結果を具体化しています。

制作時のWindows Excel 16.0（build 20430）で、基本検索、二次元検索とコピー、内側の配列、未一致・参照エラー、重複、列挿入等24ケースを確認しました。全Excel版での動作や処理速度の比較を実測したものではありません。