Excel・VLOOKUP関数で一番左に検索値がない場合にはどうすればいい？　OFFSET関数・MATCH関数

ExcelのVLOOKUP関数はデータを検索できる便利な機能です。

ただ、使うためには検索値が一番左になければいけません。検索値が一番左になかった場合の対処方法をまとめてみました。

VLOOKUP関数は検索値が一番左にあることが必要

VLOOKUP関数は便利でよく使っています。

検索値をもとに、別の表からデータを取り出すことができます。

例えば、旧ソフトのデータがあるけど、新しいソフトのデータと形式が全然違うじゃん、というときには、VLOOKUP関数を使うことで、今あるデータを活かして、新しいソフトのデータ形式に変えることができます。

以前、UiPathというロボットに入力をお願いするために入力用の形式に表を整理したときにもVLOOKUPで助かっています。

人が入力するとおりにプログラミングする必要があるため、科目ではなく、4桁のコードで表示する必要がありました。（このソフトがかなり旧ザクのイメージなんですが、他も似たり寄ったり。コスパで選んでいます。）

 

ちなみにVLOOKUPについてはこちらの記事で解説しています。

投稿が見つかりません。

OFFSET関数とMATCH関数

VLOOKUP関数を使うには、検索値が範囲の一番左側にあることが必要です。

ただ、ときには抽出したいデータが検索値より左にあるという場合もあるかと。

その場合、VLOOKUP関数が使えません。

対処法としてかんたんなのは、隣の列に空欄があれば、検索値を一番左（ここではA列）に表示させて、検索値を1番左側にもっていく方法。

 

もう1つは、VLOOKUP関数を使わないで、OFFSET関数とMATCH関数を使う方法です。

OFFSET関数

OFFSET関数というのは、あるセルを基準にしてそこから指定した行数と列数を移動させた後のセルの情報を表示するというものです。

例えば、D4セルに

とした場合、D8を基準にして下に3行、右に4列移動したところ、H11セルの2,650,000が表示されます。

左に移動したい場合には？

ちなみに左に移動したい場合にはマイナスを使って表示します。

D8セルから下に-2、右に-2（つまり、上に2、左に2）するとB6セルにある「直系」が表示されます。

これだけだと使いどころがよくわからないので、今度はMATCH関数を見てみます。

MATCH関数

MATCH関数は、こちらで指定した検索値を検索範囲の中から探し出して、それが何番目にあるかを数値で表示するものです。

例えば、D4セルに[=MATCH(A18,A9:A24,0)]という数式。

A18のセルの値、つまり10,000,000はA9〜A24のうち何番目にあるか？です。数式の最後は完全一致させたいので「0」を入力すると、

D4のセルでは[10]と表示してくれるわけです。

 

OFFSET関数×MATCH関数で検索値を表示する

検索値が一番左にない場合、VLOOKUP関数は使えないということになります。

そこで、前述のOFFSET関数とMATCH関数を組み合わせることで、同じように表示させることができます。

ここでは、B2セルにある相続税の実効税率13.5％より有利な税率となる贈与額（超えない値）で贈与分岐点を算出するというパターンを見てみます。

その算式はこうなります。

これをみてもなんのこっちゃとなるかと。数式が少々複雑なので、1つずつ考えてみます。

MATCH関数で何をやっている？

まずは（　）の中にあるMATCH関数だけを見てみます。

MATCH関数は検索値が検索範囲の中で何番目にあるか、その値を表示するということでした。

ここでは、B2の相続税の税率を基準値とし、検索範囲を贈与税の実効税率（点線で囲った部分）としています。

最後の[1]の意味は（照合の種類）を数字で表示します。[1：以下、0：完全一致、-1：以上]のどれか。

完全一致とはならないので、ここでは[1：以下]を選んでいます。

検査値（B2の相続税の税率）以下の最大の値を検索します。

そうすると、範囲の中の上から7番目ということで、この計算で返される値は[7]になります。

これをOFFSET関数と組み合わせ

やや複雑な感はありますが、もう少し。MATCH関数をOFFSET関数に組み合わせます。

このMATCH関数の部分は[7]になるのでしたから、

と読みかえることができます。

ということで、D8セルを基準にして下に7行と、-3なので左に3列移動したところのセルの値、7,000,000を表示してくれることになり、VLOOKUPと同じような表示をすることができました。

ちょっと複雑ゆえ盛り込みすぎたかもしれませんが、VLOOKUPを使うなら、知っておくと便利な関数です。

 

---

【編集後記】
昨日は法人の決算処理を中心に。勘定科目内訳書の入力をUiPathでの入力に移行しようと、そのプログラミングを少々。ひとまず一番手間がかかるところはなんとか設計できました。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
UiPathで内訳書入力
スタバ シナモンロール

---

相続税申告・ひとりしごとをサポートします　植村豪税理士事務所

相続や贈与のことでお悩みの方、「決算書の数字が読めない」、「資金繰りを改善したい」、「クラウド会計を使ってみたい」というひとり社長やフリーランスの方のサポートに力を入れています。