XLOOKUPやVLOOKUPは、検索値をもとにデータを探してくれます。
ただ、検索値に該当がないとエラーになります。今回はエラーになった場合の解決策をまとめてみました。
XLOOKUPで当日レートを見つける
今回の事例では、ドルを円換算するという事例です。円換算するのに当日レート（TTM）を使います。
このとき、必要になる当日レートを入力してはいけません。
どこかにデータがないかを探し、あれば、それを活かしましょう。
ネットを探せば何かしらあります。多くの場合は加工すれば使えます。
データをダウンロードして、Excelに貼り付け。
年表示を置換えで修正しておき、
XLOOKUPで日付を検索値として、別シート（2022ドル）に貼りつけたTTMレートのデータを検索範囲とすれば、
レートが表示されます。…と思ったら1つだけ#N/Aの表示になっています。
これは2月11日のレート（TTM）が検索範囲にないからです。
この場合、前日のレートを入力したほうが早いのでしょうが、例外をつくると次に同じことをやるときに困ります。
数式にしておけば、次に同じことをやるときにデータをコピペすれば済みます。
Excelで解決しましょう。
Excelで前日レートを出すには
XLOOKUPを使って前日レートを表示させることができます。検索値がないのに、です。
2022年の年間TTMを「2022ドル」というシートにあるという前提で、
通常はこのような数式にします。が、これだとエラーになるのは前述したとおりです。
=XLOOKUP(A2,’2022ドル’!$A:$A,’2022ドル’!$B:$B)
実はXLOOKUPは上記の数式に次のように続けることができます。
=XLOOKUP(A2,’2022ドル’!$A:$A,’2022ドル’!$B:$B,,-1)
具体的には、「,,-1」とすると該当がない場合に「次に小さい項目」を選ぶことができます。
今回の場合だと2月11日のレートがないので、前日の2月10日のレートを表示してくれます。
このレートですね。
ChatGPTに聞いてみると
ChatGPTにも解決策を聞いてみたところ、VLOOKUPでの解決策を教えてくれました。
ちゃんとTTMを列と読み取っているのがすごいなと。
で、実際にやってみると、エラーです。検索範囲も違えば、
列番号も違います。3列目はありませんから。
気を取り直して、検索範囲、列番号を「2」に変更してみると、データは表示されましたが、先程とは違うレートです。
これは検索値を「A-1」とすることで、すべてのデータで前日のレートを探してきています。
ChatGPTの回答を見ると、確かにそのような意図でした。「各行に前日のレートを…」と。
わたしの伝え方がよくないのかもです。
今度はVLOOKUPやXLOOKUPでどうやるのかも聞いてみましたが、結果は同じです。
VLOOKUPの場合と
XLOOKUPの場合、それぞれで同じような回答。
ということで、これ以降はヒトが対応するところです。
VLOOKUPの場合は、次のように「A2-1」を「A2」とし、FalseをTrueにし「近似一致」にします。
セル
A2セル=VLOOKUP(A2,’2022ドル’!$A:$B,2,TRUE)
これでXLOOKUPの場合と同じレートになりました。
XLOOKUPの検索方法については、過去にも書いています。
Excelの日付表示。XLOOKUPでエラーになる理由・月末や月初を表示する関数。 – GO for IT 〜 税理士 植村 豪 Official Blog
Excel関数（VLOOKUP・XLOOKUP・HLOOKUP）で推移表データから数字を抽出するパターン。 – GO for IT 〜 税理士 植村 豪 Official Blog
ExcelのXLOOKUPでできること。MFやfreeeの推移表に同じデータ（検索値）が2つある場合の解決策 – GO for IT 〜 税理士 植村 豪 Official Blog
入力しなくてもすでにあるデータを利用すれば、入力しなくても済みますし、エラーになったとしてもExcelでなんとかできます。
ということで、参考にしていただけるとうれしいです。
【編集後記】
昨日はじぶんの月次や請求書の送信、確定申告などを。新カメラのα7Ⅳで屋外での撮影。夜に子どもたちの写真を撮ってファインダー越しに見せてみると、「撮りたい」となり、おそるおそる渡して撮影してもらいました。で、けっこういい写真撮ってくれました。
【昨日の1日1新】
※「1日1新」→詳細はコチラ
iPhoneでα7Ⅳの遠隔撮影
