Excel。MATCH関数は値を検索してその相対位置を算出する関数です。【MATCH】

Excel。MATCH関数は値を検索してその相対位置を算出する関数です。

＜関数辞典：MATCH関数＞

MATCH関数

読み方： マッチ　　

分類： 検索/行列 

MATCH(検査値,検査範囲,[照合の種類])

値を検索してその相対位置を算出します

Excel。2024/3/24-3/30にMATCH関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/3/24-3/30にMATCH関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

3月24日

Excel。

MAKEARRAY関数

読み方： メイクアレイ　　

分類： 論理 

MAKEARRAY(rows,column,function)

LAMBDA関数を適用して、指定した行と列のサイズの計算配列を返します

3月25日

Excel。

MAP関数

読み方： マップ　　

分類： 論理 

MAP(array,lambda_or_array2,…)

LAMBDAを適用して新しい値を作成することにより、配列内の各値をmappingで形成された配列を新しい値に返す

3月26日

Excel。

MATCH関数

読み方： マッチ　　

分類： 検索/行列 

MATCH(検査値,検査範囲,[照合の種類])

値を検索してその相対位置を算出します　

3月27日

Excel。

MAX関数

読み方： マックス　　

分類： 統計 

MAX(数値1,[数値2],…)

最大値を算出します　

3月28日

Excel。

MAXA関数

読み方： マックスエー　　

分類： 統計 

MAXA(値1,[値2],…)

数値・文字列・論理値を含む最大値を算出します　

3月29日

Excel。

MAXIFS関数

読み方： マックスイフズ　　

読み方： マックスイフエス

分類： 統計 

MAXIFS(最大範囲,条件範囲1,条件1,…)

条件により指定した範囲内の最大値を算出します　

3月30日

Excel。

MDETERM関数

読み方： エムデターム　　

読み方： マトリック ディターミナント

分類： 数学/三角 

MDETERM(配列)

配列の行列式を算出します　

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい【furigana】

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい

＜VLOOKUP関数・PHONETIC+INDEX+MATCH関数＞

氏名からフリガナを抽出したい時には、PHONETIC関数をつかうことで、表示できます。

ところが、PHONETIC関数は、文字を入力した時のデータを表示するため、VLOOKUP関数やセル参照などで、間接的に表示されているデータからフリガナ情報を表示することができません。

では、どのようにしたら、次のように、フリガナを表示することができるのでしょうか。

B2には、VLOOKUP関数の数式を設定してあります。

=VLOOKUP(A2,A5:F14,2,FALSE)

C2に、PHONETIC関数をつかった数式を設定してみても、何も表示されません。

C2に、

=PHONETIC(B2)

と設定していますが、表示されないわけですね。

解決するポイントは、文字入力が入っている元のデータをつかえばいいわけです。

では、次のようにC2の数式を修正していきます。

=PHONETIC(INDEX(B5:B14,MATCH(B2,B5:B14,0)))

これで、フリガナを表示することができるようになります。

それでは、この数式を説明していきます。

最初のPHONETIC関数は、「フリガナ」情報を表示することができる関数ですね。

INDEX関数で指定した行列番号が交差するセル参照する関数ですね。

そして、INDEX関数と相性抜群のMATCH関数を組み合わせます。

MATCH(B2,B5:B14,0)

と数式を設定していますが、B2の値は、B5:B14のなかで、何番目にあるのかを算出することができます。

MATCH関数は、範囲内にある検索値の位置を算出する関数です。

このように、INDEX関数とMATCH関数を合わせることで、元にあるフリガナ情報を抽出することができます。

INDEX＋MATCH関数は、覚えておくといい、関数の組み合わせかもしれませんね。

Excel関数辞典 VOL.48。MATCH関数～MDURATION関数【dictionary】

Excel関数辞典　VOL.48。MATCH関数～MDURATION関数

＜Excel関数＞

今回は、LOGINV関数～LOWER関数までをご紹介しております。

MATCH関数

マッチ

値を検索してその相対位置を算出

MATCH(検査値,検査範囲,[照合の種類])

MAX関数

マックス

最大値を算出

MAX(数値1,[数値2],…)

MAXA関数

マックスエー

数値・文字列・論理値を含む最大値を算出

MAXA(数値1,[数値2],…)

MAXIFS関数

マックスイフズ

条件により指定した範囲内の最大値を算出

MAXIFS(最大範囲,条件範囲1,条件1,…)

MDETERM関数

マトリック ディターミナント

配列の行列式を算出

MDETERM(配列)

MDURATION関数

エムデュレーション

額面価格を $100 と仮定して、証券に対する修正マコーレー デュレーションを返します。

Modified DURATION

MDURATION(受渡日,満期日,利率,利回り,頻度,基準)

Excel。ガントチャートで期間の「始点～終点」を見出しから抽出してつくりたい。【Gantt chart】

Excel。ガントチャートで期間の「始点～終点」を見出しから抽出してつくりたい。

＜ガントチャート：INDEX+MATCH関数＞

スケジュール期間をわかりやすく管理するガントチャートという表があります。

作業Aは、6月1日に最初の○があるので、B列は、6/1～と表示され、6月3日に最後の○があるので、6/3と判断した結果、B2には、「6/1～6/3」と自動的に算出し表示しています。

今回は、この期間の作り方を紹介していきます。

どうしたら、最初の「○」がある日付を抽出することができるのかを考えていきましょう。

C1:G2までの表で考えるとわかりやすくなります。

日付がある行は、1行だけなので、行数は1で、列は、5列ある表。

つまり、1行5列の表の中から、最初の「○」がある6月1日を抽出させるには、6月1日は、1行1列のデータを抽出すればいいわけです。

このような、行と列から抽出する時には、INDEX関数を使うことで算出することができます。

問題は、最初の「○」が1列目で、最後の「○」は3列目にありますが、この1とか3をどうやって計算させればいいのでしょうか？

何番目に「○」があるのか？

このような時には、MATCH関数を使うことで算出することができます。

B2をクリックして、数式をつくっていきます。

INDEX関数は、2種類選べる関数になっているので、手入力の方がわかりやすいかと思いますので、手入力でつくることをお勧めします。

B2の数式は、
=INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0))

2020/6/1という算出結果がでました。結果がシリアル値の場合は、日付型に変更しましょう。

この計算式について説明します。

INDEX関数は、INDEX(配列,行番号,列番号)という引数をもっていますので、
配列には、$C$1:$G$1　を設定します。

オートフィルで数式をコピーすることを考慮して、絶対参照も合わせて設定しておきます。

行番号は、1行なので、1。
列番号は、最初の「○」がある場所の数値ですが、この数値をMATCH関数で算出します。

MATCH関数を確認します。
MATCH関数は、MATCH(検索値,検索範囲[,照合の種類])という引数を持っています。

検索値には、「○」。
検索範囲には、C2:G2。

照合の種類ですが、0を設定します。0は、完全一致する値を算出します。

また、最初に登場する値を算出することができます。

よって、0を設定することで、「1」を得ることができるというわけです。

なお、「0」の代わりに「-1」にすると、検索値の中で最小値を算出してくれるので、「-1」でもかまいません。

このように、INDEX関数とMATCH関数を組み合わせることで、抽出することができます。

この数式を、「～」で挟む数式に修正していきます。

=INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0))&"～"&INDEX($C$1:$G$1,1,MATCH("○",C2:G2,1))

ちょっと、長くなりましたが、「&"～"&」で始点の見出しの日付から終点の見出しの日付までという形で表示することができます。

ただ、表示形式が解けてしまうので、シリアル値で表示されてしまっています。

日付に表示形式を変更したいのですが、文字結合していることもあって、通常の表示形式の変更ではかわりませんので、TEXT関数をつかってあげる必要があります。

よって、最終的な数式は、
=TEXT(INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0)),"m/d")&"～"&TEXT(INDEX($C$1:$G$1,1,MATCH("○",C2:G2,1)),"m/d")

とさらに長くなってしまいましたが、オートフィルで数式をコピーして完成です。

ちょっと長い数式ではありますが、作業工程に変化があった場合でも、この表の場合だと、「○」を追加削除するだけで、期間がオートマチックに連動して変更することができるので、ミスが抑制できるかもしれませんね。

Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!【XLOOKUP】

Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!

＜INDEX+MATCH関数・XLOOKUP関数＞

次のような合計値に応じてランクを決定する表があります。

数値をもとにして、ランクの表から検索するので、「VLOOKUP関数」を使うように思われますが、このケース。

VLOOKUP関数では、対応することができません。

C2:C10を一度クリアして、VLOOKUP関数で算出してみましょう。

C2には、
=VLOOKUP(B2,$F$2:$G$7,1,TRUE)
という数式になるはずです。

しかし、「#N/A」というエラーが表示されてしまいます。

検索値は、B2だし、範囲も問題ない。
抽出したいのは、ランクなので、列番号は「1」。
完全一致ではなく、近似値なので、「TRUE」と大丈夫なような気がしますが、VLOOKUP関数のルールを逸脱しています。

１つ目は、検索値の左側に抽出したいデータがある。
今回はG列の合計の左にランクがある。
抽出したいものが検索値の右側にないとVLOOKUP関数を使うことができません。

では、F列とG列を入れ替えればいいのかというと…

C2の数式も合わせて次のように修正します。

=VLOOKUP(B2,$F$2:$G$7,2,TRUE)
オートフィルを使って数式をコピーしたところ、エラーのセルがあります。

実は、まだVLOOKUP関数のルールから逸脱しています。

2つ目として、範囲のF2:G7の表ですが、今回のような数値の場合は、【昇順】でなければいけません。

商品コードや氏名の場合は、ルール上よろしくありませんが、完全一致のケースが多く、検索されます。

なので、次のようにF列を昇順にすると、問題なく検索してくれます。

しかしながら、通常数値の高い順でリストをつくるだろうし、Sが何点以上というように表を閲覧するはずです。

よって、最初の表では、VLOOKUP関数を使うことはできないわけです。

では、どのようにしたらいいのかというと、このケースでの定番「INDEX+MATCH関数」をつかうことで、検索することができます。

さらに、F1:G7の表だけではINDEX+MATCH関数でも、検索することはできません。

H列の「～まで」という数値を用意する必要があります。

B2に、INDEX+MATCH関数で数式を作ってみましょう。

=INDEX($F$2:$F$7,MATCH(B2,$H$2:$H$7,-1),1)

これで、検索することができるのですが、数式が非常に「難解」ですね。

数式の説明をしてきます。

MATCH関数がどのような動きをしているのか確認してみましょう。

D2の数式は、
=MATCH(B2,$I$2:$I$7,-1)
検索値は、B2で198を指します。

検索範囲は、$I$2:$I$7
照合は、「-1」とします。「-1」は、検索値以上の最小値を検索します。

すると、上から3コ目が該当するので、3と算出されました。

それを受けて、E2のINDEX関数を確認しましょう。
=INDEX($G$2:$G$7,D2)
G2:G7の範囲で、上から、D2、つまり、3番目を検索するので、「B」を検索してくれるわけです。

このように、検索する表をそのまま使用して、検索することは容易ではありませんでした。

しかし、Office365のExcel　 Insiderで搭載された、「スピル」そして、新しく登場した【XLOOKUP関数】をつかうと、INDEX+MATCH関数のような苦労はせずにすむようになりました。

D2にXLOOKUP関数の計算式をつくっていきます。

=XLOOKUP(B2:B10,G2:G7,F2:F7,,-1,1)
これで、あっという間に算出してくれます。

範囲の表が、どうのこうのというような条件はありません。

XLOOKUP関数は、スピル機能を使う関数なので、オートフィルを使って数式をコピーする必要はありません。

また、絶対参照で範囲の表を固定する必要もありません。

それでは、数式を説明しましょう。

まず、XLOOKUP関数は、
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
という形式になっています。

検索値は、B2:B10。
合計点がどのランクなのか検索するので、B2:B10と設定します。

検索範囲は、G2:G7。検索値に該当するのを検索する範囲です。

戻り範囲は、F2:F7。検索結果の範囲です。

このように、検索範囲と戻り範囲を別々に設定できるようになったので、検索範囲を範囲の表の一列目にないといけないというルールから解放されたわけです。

見つからない場合は、今回はないので、省略。

一致モードは、完全一致かどうか決めるところです。
VLOOKUP関数では省略すると、「近似値」になってしまいましたが、XLOOKUP関数では、省略すると「完全一致」になります。

今回は、完全一致または、次に小さい項目の「-1」を設定します。

1件目は198なので、198の次に小さい項目は、190が該当しますので、Bを検索するわけです。

最後の、
検索モードは、リストの上から検索させるので、先頭から末尾へ検索の「1」を選択します。

この検索モードによって、VLOOKUP関数だと、範囲の表を昇順にしておかないといけないというルールから解放されました。

このように、XLOOKUP関数は、現場で大きなインパクトを与える関数になるのかもしれませんね。

Excel。最高値を検索できても、その行にある他のデータを抽出するには？【MAX】

Excel。最高値を検索できても、その行にある他のデータを抽出するには？

＜MAX・OFFSET+MATCH関数＞

データの中から、最大値や最小値などは、MAX関数やMIN関数を使うことで、簡単に抽出することができますが、では、その該当したデータのレコードはどうやったら、抽出することができるのでしょうか？

例えば、次の表。

C列の金額の中で最大値を算出するのはMAX関数を使うので、なんら問題はありませんが、では、その日付はいつなのか？

条件付き書式をつかって行（レコード）を塗りつぶすように設定してもいいのですが、ただ単に日付が知りたい場合どうしたらいいのか？というのが今回のテーマなのです。

まずは、F1にMAX関数を使って、最大値を算出してみます。

F1の数式は、
=MAX(C2:C16)
さて、474は、3月4日に発生しているのですが、どうやったらいいのでしょうか？

検索させるとしたら、最初に、474という最大値は、何行目にあるのか算出させる。

そして、474というセルの1つめの左側に日付のデータがあるということですよね。

なので、まず何行目にあるのか確認するにはどうしたらいいのでしょうか？
そこで登場するのが、MATCH関数です。

G1にMATCH関数の結果を表示してみましょう。
G1をクリックして、MATCH関数ダイアログボックスを表示します。

検索値には、最大値の結果が算出されている、F1
検索範囲は、金額なので、C2:C16
照合の種類ですが、完全一致なので”0（ゼロ）”。

ちなみに、”1”だと、以下。”-1”だと、以上となっています。

G1の数式は、
=MATCH(F1,C2:C16,0)

結果は、4と表示されました。4件目に該当するデータがあることが算出されたわけです。

その該当するセルの1つ左側のセルを見つけるために、今度は、OFFSET関数をつかうことで、算出することができます。

では、F2をクリックして、OFFSET関数ダイアログボックスを表示しましょう。

参照は、C1。見出しじゃないかと思うかもしれませんが、OFFSET関数は、参照である起点を０（ゼロ）としています。

つまり、データの先頭であるC2を参照（起点）として、4つ下方向のセルだと、C6を指してしまいますので、C1の見出し行を参照（起点）とする必要があります。

行数は、G1に先ほどMATCH関数で算出しましたので、G1を設定します。

列数は、1つ左側なので、-1(マイナス1)と設定して、OKボタンをクリックしましょう。

F2の数式は、
=OFFSET(C1,G1,-1)
算出結果が、シリアル値で表示されてきますので、表示形式を日付にするといいでしょう。

結果は、このようになりましたね。

このように、OFFSET関数とMATCH関数を合わせて使うことで、該当するセルの行（レコード）からデータを抽出することが可能になりますよ。

ちなみに、OFFSET関数とMATCH関数をネストにすると、
=OFFSET(C1,MATCH(F1,C2:C16,0),-1)

さらに、OFFSET関数とMATCH関数とMAX関数をネストすると、
=OFFSET(C1,MATCH(MAX(C2:C16),C2:C16,0),-1)

確かに、1つの数式で算出することができますが、理解するのに時間がかかりますので、実務では、ほどほどがいいように思えますね。

Excel。VLOOKUP関数の盲点。降順の以上の範囲では検索できないのでどうしよう。【VLOOKUP】

Excel。VLOOKUP関数の盲点。降順の以上の範囲では検索できないのでどうしよう。

＜VLOOKUP関数とINDEX+MATCH関数＞

多分岐処理をしたいときに、IF関数のネストではネストが多くなりすぎて、
VLOOKUP関数を使うやり方があるのですが、ある点に注意しないと、
算出することができません。

次の表があります。

点数によって、ランク分けしたいわけですね。

ランク分けの表は同じシートに用意しました。

A列は、表示形式のユーザー定義を使って、
”以上”を表示できるようにしています。

ただし、点数が降順になっていますね。

D2をクリックしてVLOOKUP関数ダイアログボックスを表示しましょう。

検索値は、C2
それぞれの点数ですね。この点数がどのランクなのかを知りたいわけですね。

範囲には、$A$14:$B$18
ランク表ですね。見出し行は含まないで範囲選択します。
また、オートフィルで数式をコピーしますので、
絶対参照も忘れずに設定しましょう。

列番号は、2
範囲で選択した列で、検索したい列は左から2列目にありますので、2。

検索方法は、TRUE。または、1でもOKですね。
完全一致ではなくて、「内輪の数」なので、TRUEを使います。

それでは、OKボタンをクリックして、
オートフィルを使って数式をコピーしてみましょう。

なんと、検索がうまくできていませんね。

これは、範囲で選択したランク表が降順だったのが原因なのです。
検索範囲の表は、昇順でないとVLOOKUP関数は機能してくれません。

念のため、昇順の表を使ってみたらどうなるのでしょうか？

D13:E18に昇順にしたランク表を用意しました。

D列は、先ほどと同様に、表示形式のユーザー定義を使って、
”以上”と表示させています。

では、E2にVLOOKUP関数ダイアログボックスを表示して数式を作っていきます。

範囲が、$D$14:$E$18になっているだけで、先ほどと変わっていませんね。

E2の数式は、
=VLOOKUP(C2,$D$14:$E$18,2,TRUE)
すると、昇順にした範囲だと、問題なくランクを検出してくれていますね。

ということで、VLOOKUP関数で多分岐処理を行う場合には、
範囲の表を昇順にしておくことを忘れないようにしましょう。

となるのですが、もし、降順の表の場合はどうしたらいいのでしょうか？

【範囲が降順の表】

範囲の表が降順だった場合ですが、残念ながらVLOOKUP関数は使えませんので、
別の方法でトライしていきます。

A13:B18の表を、「以上」という表から、80以上ではなくて、
80以上100以下とか0以上19以下というように、
その区間を表すようにアレンジしていきます。

なんで、このC列を用意する必要があるのかというと、
点数がどこの範囲に該当しているのかを見つけるために、
MATCH関数を使うからです。

このMATCH関数の引数に昇順と降順が影響する箇所があるので、
今回は降順の表のため、C列を用意する必要があるのです。

では、わかりやすいようにMATCH関数をF2に作ってきます。

MATCH関数ダイアログボックスでもいいのですが、
意味を確認したいので、手入力で数式を作ります。

検索値は、C2
検査範囲は、C14:C18。絶対参照も忘れないようにしましょう。

そして、ここがポイントの照合の種類
1の以下は、検査範囲が昇順で配置しておく必要があるので、
今回は、「-1以上」を使わないといけません。

検索値以上とするは、C列が必要だったわけです。

F2の数式は
=MATCH(C2,$C$14:$C$18,-1)

オートフィルを使って数式をコピーしてみましょう。

最後は、算出されたMATCH関数を元に、
INDEX関数を使ってD2:D11にランクを算出していきます。

D2をクリックして、INDEX関数ダイアログボックスを表示していくと、

引数の選択ダイアログボックスが表示されますので、領域番号は不要なので、
『配列,行番号,列番号』を選択してOKボタンをクリックしましょう。

INDEX関数ダイアログボックスが表示されますので、
引数を設定していきましょう。

配列は、$B$14:$B$18
配列といっても、抽出したいランクがある範囲ですね。

行番号は、F2
MATCH関数で算出した結果を使っています。
配列で選択した範囲の中の行を見に行くという指示をしています。

列番号は、1
配列の範囲が1列なので、省略してもいいのですが、
わかりやすいように1列目を見に行くという指示をしています。

つまり、配列の範囲の、1列F2行のデータを抽出するという意味になります。

それでは、OKボタンをクリックしましょう。

このように、降順の表でも算出することができましたね。

なお、D2の数式は、
=INDEX($B$14:$B$18,F2,1)

となっていますが、今回はわかりやすいように、
MATCH関数をF列に事前に算出させていますが、
当然、一つにまとめることができます。まとめた数式は、

=INDEX($B$14:$B$18,MATCH(C2,$C$14:$C$18,-1),1)

確かに降順の表を使っても抽出することはできますが、
昇順の表を用意するほうが楽な気もしますので、
どうしても、昇順の表が用意できない場合は、
このような方法もある程度でいいような気もしますね。

Excel。表からTOP3のデータを抽出したいけどどうしたらいいの？

Excel。表からTOP3のデータを抽出したいけどどうしたらいいの？

＜INDEX&MATCH&LARGE関数＞

TOP3の値なんて、
LARGE関数を使えば簡単に算出できるでしょう？と思われるでしょうが、
今回は、次の表からTOP3のデータを抽出したいというのがリクエストなのです。

左側のA2:C12までの表があります。
このデータの中から、E:Gにある、
別表にTOP3までのデータを抽出したいということなのです。

オートフィルターを使ってもいいのですが、
今回は、関数を使って抽出を行ってみたいと思います。

【LARGE関数だけでは難しい】

まず、1位だけならば、MAX関数を使うところですが、
2位・3位はMAX関数ではダメなので、登場するのがLARGE関数ですね。

しかし、このLARGE関数では、売上高のTOP3を算出することが出来ても、
店舗名を抽出することは出来ません。

仮に、LARGE関数で算出した数値を使ってVLOOKUP関数で
店舗名を算出しようとしても、売上高の左側なので、それも無理ですし、
数値が重複していた場合も考慮する必要がありますので、
LARGE関数単独では難しいようです。

そこで、このようなケースでは、INDEX関数が重宝します。

【INDEX関数とMATCH関数のコンビネーション】

INDEX関数は、
どこにデータがあるのかを教えてあげればいい関数なので、
それぞれ、1位・2位・3位の数値がデータの何件目にあるのかを教えてあげれば
データを抽出することが出来ます。

では、どのようにしたら、1位～3位までのデータがそれぞれ、
どこにあるのかを求めるには、MATCH関数を使えば求めることが出来ます。

そこで、今回は、まずはMATCH関数を使って別の列にデータを算出して、
それからINDEX関数を使っていく方法をとります。

E3をクリックして、MATCH関数ダイアログボックスを表示します。

検索値には、LARGE関数をネストしますので、
LARGE関数ダイアログボックスを表示します。

配列には、$C$3:$C$12
順位には、1という数値がある、F3
そして、数式バーを使って、MATCH関数ダイアログボックスに戻りましょう。

検索範囲には、売上高の$C$3:$C$12
照合の種類は、完全一致なので、0
そして、OKボタンをクリックします。

7と算出されました。この表の7件目のデータが1位ということがわかりました。

確かに、秋葉原の480が1位ですね。

では、E5まで数式をコピーしておきましょう。

では、この数値を使って、G3に店舗名を抽出していきます。

そこでINDEX関数を使いますので、
INDEX関数ダイアログボックスを表示します。

INDEX関数は、2種類の引数を持っている関数なのですが、
今回は、『配列・行番号・列番号』を使っていきますので、
選択してOKボタンをクリックします。

配列には、B$3:B$12　複合参照にしてあるのは、
完成した数式をオートフィルでコピーするためです。
行番号は、$E3
列番号は、0
あとは、OKボタンをクリックします。
そして、数式をオートフィルを使ってコピーします。

G3の数式は、
=INDEX(B$3:B$12,$E3,0)

これで、抽出することが出来ましたね。

このように、
INDEX&MATCH&LARGE関数のコンビネーションで抽出することが出来ましたね。

ついでに、数式を一発で算出しようとした場合は、
G3の数式は次のようになります。
=INDEX(B$3:B$12,MATCH(LARGE($C$3:$C$12,$F3),$C$3:$C$12,0),0)

やっぱり結構長い感じになりましたので、
慣れるまでは無理をせずに、
しっかり算出・抽出することを考えていきましょう。

Excel。アレレ？VLOOKUP関数の弱点！このような範囲では太刀打ちできない

Excel。アレレ？VLOOKUP関数の弱点！このような範囲では太刀打ちできない

＜VLOOKUP&INDEX+MATCH関数＞

まずは、次のような表があります。

6行目から従業員リストがあるとしまして、
B3に従業員コードを入力すると、C3に従業員名が表示されるという、
見た目、ベタベタなというか、VLOOKUP関数で、

”楽勝”と思えるような表なのですが、

実は、コレ。

VLOOKUP関数の弱点というか、
6行目からの従業員リストの作り方がなっていないというか、
これでは、VLOOKUP関数で算出することが出来ないのです。

百聞は一見に如かず。

VLOOKUP関数で算出してみましょう。

まずは、B3に従業員コードSM001というダミーデータを入力して、
C3をクリックしてVLOOKUP関数ダイアログボックスを表示します。

検索値には、B3
範囲には、A7:D11
列番号は、従業員名は、範囲の左から2列目にありますので、2
検索方法は、完全一致なので、FALSE。または、0ですね。

では、OKボタンをクリックしてみましょう。
すると、結果は…#N/A

なんで！とお思いでしょうけど、このように算出できないのです。

原因は、従業員リストにあります。

VLOOKUP関数で抽出するには、
抽出する値が検索値より【右側】になければ抽出することが出来ないのです。

要するに、次のような従業員リストだったら、よかったのです。

しかし、現実的に、このような順番に出来ない場合はどうしたらいいのでしょうか？

そこで登場するのが、
MATCH関数で検索値を見つけてINDEX関数で抽出する、

INDEX+MATCH関数のコンビネーションなのです。

まず、MATCH関数の動きを確認したほうがわかりやすいので、
D3にMATCH関数を作ってみましょう。

D3をクリックして、MATCH関数ダイアログボックスを表示します。

検索値には、B3
検査範囲には、D7:D11
照合の種類には、完全一致なので、0

そして、OKボタンをクリックしましょう。

このMATCH関数を使うと、リストの何番目にデータがあるのかがわかるようになります。

D3の数式は、

=MATCH(B3,D7:D11,0)

この算出された数値をINDEX関数で使って、抽出しようというわけです。

では、C3をクリックして、INDEX関数ダイアログボックスを表示しましょう。

INDEX関数は、２通りの引数を持つ特殊な関数ですが、

今回は行番号があればいいので、どちらでもOKなので、

配列.行番号.列番号を選んでOKボタンをクリックします。

配列には、B7:B11
行番号には、先程MATCH関数で算出したD3を使います。

OKボタンをクリックします。

C3の数式は、

=INDEX(B7:B11,D3)

これで、抽出することが出来ましたね。

このように、VLOOKUP関数の弱点というか、
範囲の表の作りによっては、VLOOKUP関数が使えないケースがありますので、
INDEX+MATCH関数を覚えておくといいかもしれませんね。

なお、C3の数式を、

=INDEX(B7:B11,MATCH(B3,D7:D11,0))

とネストにしてもOKですね。