ExcelのXLOOKUPでできること。リストに同じデータ(検索値)が2つある場合の解決策

Excel

ExcelのVLOOKUPを使ったとき、同じ検索値が2つあるというケースもあります。

その場合、似て非なるXLOOKUP関数を使うのがおすすめです。

VLOOKUPと似て非なるXLOOKUP

Excelの関数、VLOOKUP。

検索値をリストから探して、一致したデータを表示してくれます。

説明よりも見てもらったほうが早いだろうということで。

事例では、商品コードA003をもとに単価リストから、同じA003を見つけて、売上表の商品名と単価に表示させています。

GOforIT 118

IFERRORを使っている理由については、こちらの記事でまとめています。

VLOOKUP関数でエラー値を表示させないためにIFERROR関数を使う | GO for IT 〜 税理士 植村 豪 Official Blog

最低限必要な部分だけだとこういう式「=VLOOKUP(検索値,検索範囲,列番号,検索方法)」でリスト全体を検索範囲として指定します。
GOforIT 122

列番号でリストの何列目を表示するかを数字を入力します。2列目なら「2」、3列目なら「3」というように。
GOforIT 123

VLOOKUPは検索値を検索範囲(リスト)の一番左に置く必要がありました。検索値が一番左でない場合の対処法もありましたが、難易度は高いものでした。

Excel・VLOOKUP関数で一番左に検索値がない場合にはどうすればいい? OFFSET関数・MATCH関数 | GO for IT 〜 税理士 植村 豪 Official Blog

一方で、似て非なるXLOOKUP。できることは似ていますが、VLOOKUPに比べるとその使いやすさははるかに上がっている印象です。

同じ例でXLOOKUPの場合では、セルB3の検索値と同じ値をH列から探して、該当する行のJ列の値をセルD3に表示して。というようにシンプルに指定できるようになっています。

GOforIT

別の事例でも見てみます。存続年数に対応した複利現価率を表示したいという場合、

GOforIT 135

計算シートの存続期間20年を検索値に別シート「複利表」から20年の複利現価率、0.554を探して、

GOforIT

表示させることができます。

GOforIT

XLOOKUPの使い方

XLOOKUPを使うときには、「=xl」と入力してみると、XLOOKUPが表示されます。

その状態のまま、Tabキーをクリックしてみると、選べます。
GOforIT 108

すると、「検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]」と順に表示されているのがわかります。

GOforIT 110

「,(カンマ)」で区切ればこれだけの条件指定をすることができるのです。

ただ、これは少々複雑ということで。通常は「検索値」と「検索範囲」、「戻り範囲」までを入力してEnterをすれば、大丈夫です。

検索値(B3)を選び、「,(カンマ)」を、そのあと、検索範囲としてH列を選び、「,(カンマ)」、戻り範囲で、J列を指定し、

GOforIT 120

Enterを押すと、このように単価が表示することができます。
GOforIT 121

ただし、この場合は完全一致をしたものだけを探すので、完全一致ではなく、このような近似値検索(VLOOKUPで言うとFALSEでなくTRUE)をしたい場合、

GOforIT 115

末尾から先頭に向かって検索したいなどパターンを変えたい場合には、「,(カンマ)」をつけて先送りして指定することが必要です。

GOforIT 124

その場合でも指定しない[見つからない場合]は、「,(カンマ)」を連続して「,,」のように入力し、指定しなくても問題ありません。次の条件指定に移ります。

GOforIT

指定しようとしている場所のフォントが太くなるので、どこを指定しようとしているかがわかりやすいのも気にいっているところです。
GOforIT

これだと[一致モード]を指定しようとしているわけです。

同じ検索値が2つある場合の解決策

検索モードが使える場面はどんな場面だろう?

ということで。こちらも実例でご紹介します。(実例と行っても数字は事例ですが、わたしがやったことです。)

会計ソフトのデータをダウンロードして、Excelで表示させたいというのが今回の事例。

ExcelのシートにダウンロードしたCSVデータを、準備したファイルのデータ貼り付け用のシートにそのまま貼り付けて、
GOforIT 125

別のシートに千円単位で表示させるという流れです。

これをXLOOKUPを使ってやってみると、「売上高合計」という項目を会計データから検索して、

GOforIT 125

 

このように売上高926(千円)を表示させることができます。
GOforIT 126

ただ、同様に売上原価でやってみると、なぜか数字はゼロになってしまいます。数字はあるはずなのに。

GOforIT 128

サンプルの会計データは、マネーフォワードクラウドのデータですが、freeeの月次推移表をダウンロードしても、
GOforIT 117

A列に同じ科目が表示されることがあります。やはり上の売上には数字が入っていない、下の売上高には数字が入っています。(事例によるかもですが、どちらのデータもいらない行がちょいちょいあるような…)
GOforIT 114

この原因は、同じ検索値が2つあることです。上から順番に探していくので、何もデータがない上の売上原価のデータをもってきます。結果として値はないのでゼロ。

GOforIT 111

ではどうするか?

この問題もXLOOKUPを使って解決することができます。「=XLOOKUP($A5,推移表データ!A:A,推移表データ!D:D,,,-1)」として[検索モード]で末尾から検索するを選ぶと、

GOforIT

無事、欲しい数字が表示されました。

GOforIT 112

まさに末尾から検索してくれれば、先に下の売上原価がヒットするわけです。

GOforIT 111

VLOOKUPに比べてXLOOKUPではできることも増えたし、使いやすくなっているという印象です。

こういうのはやらなくてもよくなりましたし。

Excel・VLOOKUP関数で一番左に検索値がない場合にはどうすればいい? OFFSET関数・MATCH関数 | GO for IT 〜 税理士 植村 豪 Official Blog

Excelを使うならXLOOKUPを使える場面は結構多いかと思うので、気になる方は試してみていただければ。


【編集後記】
昨日はオフ。朝、私用で外出。戻ってきてからは暑すぎて家でのんびりと。無印良品の塩バウムは美味でしたね。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
無印良品 塩バウム