前回のコラムでは、Excelの定番検索関数「VLOOKUP」をご紹介しました。
便利な一方で、「列単位でしか検索できない」「表の並びに制限がある」と感じた方もいるかもしれません。
そんなモヤっとを解消するのがXLOOKUP関数。
XLOOKUP関数は行や列のどちらにも対応しています。
これにより、データ構造が複雑でも柔軟に対応でき、列の位置に関係なく必要な情報を簡単に見つけ出すことができます。
本記事では、XLOOKUP関数の便利な使い方を解説していきます。
XLOOKUP関数は、Excel 2019以降で使える検索関数です。
一言で言うと、表の中から指定した答えを見つけてくれる機能。
VLOOKUP関数は、検索列より右側の列からしかデータを取得できません。
一方、XLOOKUP関数は左右どちらの方向でも自由に検索できます。
単価照会はもちろん、単価表と連動した見積書づくりも簡単に行えます。
VLOOKUPの弱点を補う形で生まれた、初心者にもやさしい検索関数です。
セルに「＝X」と入力し、一覧に「XLOOKUP」と表示されれば、お使いのバージョンでXLOOKUP関数を利用できます。
仕組みはとてもシンプル！
考え方はたったの3つです。
1. 何を探すか (例：資材名、資材コード)
2. どこで探すか (例：資材名の列)
3. 何を表示したいか
これだけ決めれば、XLOOKUP関数が代わりに探してくれます。
例として、「機材名」を入力するだけで、「機材コード」と「単価」が 一度に 自動入力されるように作ってみます！
では、実際に関数を入力してみましょう。
今回は前回のVLOOKUP記事で紹介したものと少し方法変えて、別シートにデータを用意してみます。
検索結果は シート1 に表示させて、
機材名、機材コード、単価が入った単価一覧表は シート2 に準備します。
次に、XLOOKUP関数を入力していきます。
表示したいセル、ここでは シート1 の「機材名」に関数を入力します。
=XLOOKUP( 検索値①, 検索範囲②, 戻り範囲
③, [見つからない場合の値], [一致モード], [検索モード])
①検索値：探したい値（数値、文字列、日付など）
②検索範囲：検索値を探す範囲
③戻り範囲：結果として表示したい値が含まれる範囲
・見つからない場合の値（省略可）：一致しない時に表示する値
・一致モード（省略可）：完全一致や近似一致などの指定
・検索モード（省略可）：検索の方向を指定（先頭から、末尾から など）
▼数式をコピーしたい方はこちら=XLOOKUP(A3,Sheet2!A3:A16,Sheet2!B3:C16)
これでXLOOKUP関数の入力がおわりました！
ではさっそく、機材コードを入力して単価がいくらなのか調べてみましょう。
A3の機材コードのセルに、単価一覧表内にある「PP00」というコードを入力し、
Enterキーを押します。
機材名、単価が自動で表示されました。
ここからは、“こんな便利機能も！” のご紹介です。
ワンポイントアドバイス
機材コードに、機材一覧表にない文字を入力してしまうと、
このように、エラー表示が出てしまいます。
・まだ一覧表に情報を入力している状態だから表示させたくない..
・会議で使用するので、なるべくきれいに資料を作成したい..
という理由で、なるべくエラー表示をさせたくない時もあるかと思います。
そのような時、関数の最後に ,”” を入力するだけで、#N/Aのエラー表示を消して空白表示にできるんです！
▼数式をコピーしたい方はこちら=XLOOKUP(A3,Sheet2!A3:A16,Sheet2!B3:C16,"")
たった3文字の記号をいれるだけで、空白表記になり、スッキリとしたデータに編集できます。
XLOOKUP関数はVLOOK関数より格段に使いやすく、自由度が高いのが特徴です。
仕組みがシンプルなので、ぜひ使ってみてください！
足場が組めるなら、関数だって組める。Excelは、慣れれば必ず使いこなせます。