ExcelのVLOOKUPを使ったとき、同じ検索値が2つあるというケースもあります。
その場合、似て非なるXLOOKUP関数を使うのがおすすめです。
VLOOKUPと似て非なるXLOOKUP
Excelの関数、VLOOKUP。
検索値をリストから探して、一致したデータを表示してくれます。
説明よりも見てもらったほうが早いだろうということで。
事例では、商品コードA003をもとに単価リストから、同じA003を見つけて、売上表の商品名と単価に表示させています。
IFERRORを使っている理由については、こちらの記事でまとめています。
VLOOKUP関数でエラー値を表示させないためにIFERROR関数を使う | GO for IT 〜 税理士 植村 豪 Official Blog
最低限必要な部分だけだとこういう式「＝VLOOKUP（検索値,検索範囲,列番号,検索方法）」でリスト全体を検索範囲として指定します。
列番号でリストの何列目を表示するかを数字を入力します。2列目なら「2」、3列目なら「3」というように。
VLOOKUPは検索値を検索範囲（リスト）の一番左に置く必要がありました。検索値が一番左でない場合の対処法もありましたが、難易度は高いものでした。
Excel・VLOOKUP関数で一番左に検索値がない場合にはどうすればいい？ OFFSET関数・MATCH関数 | GO for IT 〜 税理士 植村 豪 Official Blog
一方で、似て非なるXLOOKUP。できることは似ていますが、VLOOKUPに比べるとその使いやすさははるかに上がっている印象です。
同じ例でXLOOKUPの場合では、セルB3の検索値と同じ値をH列から探して、該当する行のJ列の値をセルD3に表示して。というようにシンプルに指定できるようになっています。
別の事例でも見てみます。存続年数に対応した複利現価率を表示したいという場合、
計算シートの存続期間20年を検索値に別シート「複利表」から20年の複利現価率、0.554を探して、
表示させることができます。
XLOOKUPの使い方
XLOOKUPを使うときには、「=xl」と入力してみると、XLOOKUPが表示されます。
その状態のまま、Tabキーをクリックしてみると、選べます。
すると、「検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]」と順に表示されているのがわかります。
「,（カンマ）」で区切ればこれだけの条件指定をすることができるのです。
ただ、これは少々複雑ということで。通常は「検索値」と「検索範囲」、「戻り範囲」までを入力してEnterをすれば、大丈夫です。
検索値（B3）を選び、「,（カンマ）」を、そのあと、検索範囲としてH列を選び、「,（カンマ）」、戻り範囲で、J列を指定し、
Enterを押すと、このように単価が表示することができます。
ただし、この場合は完全一致をしたものだけを探すので、完全一致ではなく、このような近似値検索（VLOOKUPで言うとFALSEでなくTRUE）をしたい場合、
末尾から先頭に向かって検索したいなどパターンを変えたい場合には、「,（カンマ）」をつけて先送りして指定することが必要です。
その場合でも指定しない[見つからない場合]は、「,（カンマ）」を連続して「,,」のように入力し、指定しなくても問題ありません。次の条件指定に移ります。
指定しようとしている場所のフォントが太くなるので、どこを指定しようとしているかがわかりやすいのも気にいっているところです。
これだと[一致モード]を指定しようとしているわけです。
MFやfreeeの推移表で同じ検索値が2つある場合の解決策
検索モードが使える場面はどんな場面だろう？
ということで。こちらも実例でご紹介します。(実例と行っても数字は事例ですが、わたしがやったことです。）
マネーフォワードやfreeeのデータをダウンロードして、Excelで表示させたいというのが今回の事例。
ExcelのシートにダウンロードしたCSVデータを、準備したファイルのデータ貼り付け用のシートにそのまま貼り付けて、
別のシートに千円単位で表示させるという流れです。
これをXLOOKUPを使ってやってみると、「売上高合計」という項目を会計データから検索して、
このように売上高926（千円）を表示させることができます。
ただ、同様に売上原価でやってみると、なぜか数字はゼロになってしまいます。数字はあるはずなのに。
サンプルの会計データ、freeeの月次推移表をダウンロードして、
A列に同じ科目が表示されることがあります。やはり上の売上には数字が入っていない、下の売上高には数字が入っています。（事例によるかもですが、どちらのデータもいらない行がちょいちょいあるような…）
この原因は、同じ検索値が2つあることです。上から順番に探していくので、何もデータがない上の売上原価のデータをもってきます。結果として値はないのでゼロ。
ではどうするか？
この問題もXLOOKUPを使って解決することができます。「=XLOOKUP($A5,推移表データ!A:A,推移表データ!D:D,,,-1)」として[検索モード]で末尾から検索するを選ぶと、
無事、欲しい数字が表示されました。
まさに末尾から検索してくれれば、先に下の売上原価がヒットするわけです。
VLOOKUPに比べてXLOOKUPではできることも増えたし、使いやすくなっているという印象です。
こういうのはやらなくてもよくなりましたし。
Excel・VLOOKUP関数で一番左に検索値がない場合にはどうすればいい？ OFFSET関数・MATCH関数 | GO for IT 〜 税理士 植村 豪 Official Blog
Excelを使うならXLOOKUPを使える場面は結構多いかと思うので、気になる方は試してみていただければ。
【編集後記】
昨日はオフ。朝、私用で外出。戻ってきてからは暑すぎて家でのんびりと。無印良品の塩バウムは美味でしたね。
【昨日の1日1新】
※「1日1新」→詳細はコチラ
無印良品 塩バウム
