VLOOKUPが思っている以上に壊れやすい理由
VLOOKUPは検索範囲内での列の位置を「番号」で参照しているため、その範囲内のどこかで列を挿入・削除すると、エラーメッセージが出ないまますべてのVLOOKUP数式の結果がずれてしまいます。数式自体は動き続けるので、ただ静かに間違った答えを返すだけです。この挙動こそが「昨日まで動いていたVLOOKUPが動かない」というトラブルの最も多い原因であり、規模が大きく頻繁に編集されるシートでINDEX+MATCHがよく勧められる主な理由でもあります。
関数を組み合わせてこそ真価が発揮される
実務では、これらの関数を単独だけで使うことはあまりありません。IFの中にCOUNTIFを入れ子にしたり、SUMIFの結果をVLOOKUPと組み合わせたりするのは、それぞれの関数に慣れてくるとよく見られるパターンです。まず各関数の構文を覚えることが第一歩で、特定の質問に答えるために2〜3個を組み合わせられるようになることこそが、日々の作業時間を実際に短縮してくれます。
覚える順番と練習のしかた
初心者の方は、まずSUMとAVERAGEで範囲指定に慣れ、次にIFで条件分岐を覚えると理解しやすくなります。そのあとCOUNTIFとSUMIFで簡単な集計ができるようになり、最後にVLOOKUP(余裕があればINDEX+MATCHやXLOOKUP)へ進むのが自然な流れです。練習には、自分の家計簿や勤務表のように中身を理解しているデータを使うのがおすすめです。答えが正しいかどうかを自分で判断できるので、数式の間違いに気づきやすくなります。1つの関数をまず10行程度の小さな表で試し、結果が期待どおりになってから大きな表に広げると、失敗を減らせます。
数式を渡す前のチェックリスト
他の人に渡す、または提出する前には、次の点を確認しておくと安心です。範囲に最新の行がすべて含まれているか、固定すべき参照に $ が付いているか、VLOOKUPの最後の引数がFALSEか、文字列として保存された数字が混ざっていないか、の4点です。さらに、合計値を電卓や別の方法で一度だけ検算しておくと、範囲のずれのような静かなミスをかなり防げます。なお、バージョンによって使えない関数もあるため、共有する相手の環境が分からない場合は、IFSやXLOOKUPなど比較的新しい関数の使用は控えめにするのが無難です。
よくある質問
VLOOKUPとINDEX+MATCH、実務上の違いは何ですか?
VLOOKUPのほうが書き方はシンプルで、小規模で変更の少ないシートには十分です。一方でINDEX+MATCHは、列の挿入で崩れることがなく、検索方向も左右どちらにも対応できるため、規模が大きい、あるいは頻繁に編集されるファイルにはより頑丈な選択肢です。
数式が #N/A や #VALUE! のようなエラーを表示するのはなぜですか?
#N/Aは、多くの場合、検索系の関数が一致する値を見つけられなかったことを意味します(余分なスペースやデータ型の不一致、単純な入力ミスが原因のことが多いです)。#VALUE!は、数値ではないもの(数値に見えるように書式設定されたテキストなど)に対して計算をしようとした場合によく発生します。
絶対参照はどんなときに使えばよいですか?
数式をコピーしても同じセルを参照し続けたいときに使います。たとえば消費税率を入力した1つのセルを複数の行の計算から参照する場合や、VLOOKUPの検索範囲を固定する場合です。$ を付け忘れるとコピーした先で参照がずれ、気づきにくい計算ミスになります。
VLOOKUPをやめてXLOOKUPに切り替えるべきですか?
使える環境が揃っているなら、XLOOKUPのほうが書きやすく崩れにくいのでおすすめです。ただし、Excel 2019以前を使う人とファイルをやり取りする場合は動かない可能性があります。まずは自分と相手のバージョンを確認してから選ぶのが現実的です。
合計が電卓の結果と合わないのはなぜですか?
よくある原因は3つです。範囲に新しい行が含まれていない、数字が文字列として保存されていて合計から外れている、そして非表示の行やフィルターで隠れた行を見落としている、という場合です。まずセルの配置が左寄りの数字がないかを確認してみてください。