入力した値に対応した行の複数の値をスピルでセルに表示する - XLOOKUP 関数を利用した入力値による行の参照 - Excel

ExcelのXLOOKUP関数を利用して、入力した値に対応した行の複数の値をスピルでセルに表示する手順について紹介します。

概要

VLOOKUPやHLOOKUPでは、 入力に対応したセルの値を参照できます。通常の用途では、VLOOKUPやHLOOKUPで実現できますが、 利用シーンによっては、行の複数の値をまとめて参照したい場合があります。 VLOOKUPやHLOOKUPを利用する場合は、セルのコピーペーストで対応できますが、XLOOKUP関数を利用すると、 Excelに新しく追加されたスピルの機能を利用して、行の値を簡単に参照できます。

書式

[見つからない場合の値],[一致モード],[検索モード]のパラメーターは省略可能です。 [見つからない場合の値],[検索モード]を省略する場合は次の書式になります。

使用例1

以下の表をExcelに入力します。

商品コード	名前	価格	在庫	保管場所
P-001	ぺんぎんクッキー	380	15	東京
P-002	らくだキャラメル	120	30	札幌
P-003	しろくまアイス	320	25	東京
P-004	かるがもサブレ	240	40	札幌
P-005	あるぱかキャンディ	160	8	札幌

I3セルに次の数式を入力します。
H3セルに入力された値をB3:B7セルの範囲で検索し、一致した行のC列からF列までの値をスピルで返す数式になります。


数式の入力が確定されると、I3セルの表示は #N/Aとなります。


H3セルに "P-003" を入力します。商品コードがB5セルと一致しますので、C5セルからF5セルまでの値が返されます。
I3セルから右側に向かって"しろくまアイス","320","25","東京"の値が表示されます。


I3セルより右側に表示されている値はスピルでの表示となります。青枠がついており、スピルであることがわかります。


K3セルに移動すると灰色でI3セルの数式が表示されており、スピル表示であることがわかります。


XLOOKUP関数の4つ目のパラメーターに文字列を設定すると該当する要素がない場合の表示を設定できます。4つ目のパラメーターに"見つかりませんでした"を設定します。


数式を確定します。I3セルの表示が"見つかりませんでした"になります。


B3:B7セルに存在しないコードを入力した場合も該当する行がないため、"見つかりませんでした"の表示です。


B3:B7セルに存在するコードを入力した場合は、該当する行の値が表示されます。

使用例2

横方向でのXLOOKUPの例です。
以下の表をExcelに入力します。

コード	1	2	3	4	5	6
カテゴリ	C#	CSS	Java	HTML	Delphi	Python
詳細名	C#プログラミング	CSSコーディング	Javaプログラミング	HTMLデザイン	Delphi プログラミング	Pythonプログラミング
人気度	B	B	C	B	D	A
記事数	10	5	2	7	4	6

B10セルに次の数式を入力します。
B9セルに入力された値をC2:H2セルの範囲で検索し、一致した列の3行から6行までの値をスピルで返す数式になります。


数式の入力が確定されると、I3セルの表示は #N/Aとなります。


B9セルに"4"を入力します。商品コードがF2ルと一致しますので、F3セルからF6セルまでの値が返されます。
B10セルから下に向かって"HTML","HTMLデザイン","B","7"の値が表示されます。


B9セルに"6"を入力します。商品コードがH2セルと一致しますので、H3セルからH6セルまでの値が返されます。