エクセルでXLOOKUPの使い方｜VLOOKUPとの違いと、見つからないときの出し方

商品コードから商品名や単価を引くとき、VLOOKUP の代わりに使えるのが XLOOKUP 関数です。VLOOKUP と違い、「何列目」と数えなくてよく、探す列より左の列も返せます。ただし、使えるのは新しいExcel（Microsoft 365・Excel 2021以降）だけです。

下の表で試した結果を書きます。「商品表」シートは、A列が商品コード、B列が商品名、C列が単価です（2〜5行目に4商品）。

商品表

A101 ノート 120円

A102 ボールペン 80円

A103 付箋 250円

A104 クリアファイル 60円

「注文」シートは、A列＝商品コード、B列＝数量、C列＝商品名、D列＝単価、E列＝金額です。商品名の式はC2、単価の式はD2、金額の式（=B2*D2）はE2に入れて、下へコピーします。

以下の計算結果はLibreOfficeで確認しています。Excel本体での動作確認は未実施です。

■ 基本の形

=XLOOKUP(A2,商品表!$A$2:$A$5,商品表!$B$2:$B$5,"未登録")

試した結果（A2がA102のとき）：ボールペン

この例では4つの引数を使います。

・探す値：A2（商品コード）

・どこから探すか：商品表!$A$2:$A$5（コードの列だけ）

・どこから返すか：商品表!$B$2:$B$5（商品名の列だけ）

・見つからないとき：「未登録」（省略もできます）

VLOOKUP のように表全体と「2列目」を指定せず、探す列と返す列を別々に選びます。省略しても完全一致で探し、同じコードが複数あれば上から最初の1件を返します。

■ 単価を引いて金額を出す

=XLOOKUP(A2,商品表!$A$2:$A$5,商品表!$C$2:$C$5,0)

試した結果：80

金額は =B2*D2 のように、引いた単価に数量を掛けます。数量3なら240になりました。A104・数量5なら、60で300でした。

■ 見つからないとき

商品表にないコード（A999など）を入れると、4つ目に書いた言葉が出ます。

試した結果：商品名は「未登録」、単価は 0、金額も 0

VLOOKUP のように IFERROR で包まなくても、式の中で済みます。4つ目を省略すると #N/A になります。

注意：単価を 0 にすると、未登録の商品があっても合計が計算できてしまいます。単価の式の最後を "未登録" に変える場合は、金額の式も =IF(D2="未登録","要確認",B2*D2) に変えてください。SUMは文字を合計しないので、「要確認」が残っている間は合計を確定しないでください。

■ 左の列も返せる

=XLOOKUP("付箋",商品表!$B$2:$B$5,商品表!$A$2:$A$5)

試した結果：A103

商品名の列で「付箋」を探して、左の商品コードを返しました。VLOOKUP 単独では検索列より左の列を返せません。従来は INDEX と MATCH を組み合わせるなどしていた場面も、XLOOKUP なら1つの関数で済みます。

■ 列を増やしてもずれない

VLOOKUP は「何列目」の数字を使うので、表の途中に列を足すとずれます。XLOOKUP は返す列を直接選ぶため、列を足しても引く場所が変わりません。

■ 使えない場合

・Excel 2019 以前では #NAME? と出ます。ほかの人にファイルを渡すときは、相手のExcelの版を確かめてください。

・版が分からないときや、古いExcelの人と共有するときは、VLOOKUP のほうが無難です。

表の作りが複雑で組み直す時間がないときは、専用の調整サービスもあります。