表の中から条件に一致する特定の値を参照したい とき、
VLOOKUP関数
HLOOKUP関数
INDEX関数＋MATCH関数
XLOOKUP関数（2020年以降なら）
などを使う方法がある。

これらの関数を駆使すれば、表の中から条件に一致する特定の値を持ってきたいとき大抵の場合上手くいくのだが、この間仕事で詰まったことがある。

その時やりたかったことが、
・IDが一致する
・日付が「～以上」である
・日付が「～以下」である
という３つの条件に一致する行の値を抽出したいというもの。

上で挙げた関数たちは、基本的にただ条件に一致するものを持ってくるのは得意なのだが、
「～以上」とか「～以外」とかの条件 を指定しようとするとちょっとテクニックが必要となる。

しかも今回はそれを 「～以上」 と 「～以下」 で 複数条件 ・・・
複数条件となると、私の知っているちょっとしたテクニックでもうまくいかない・・・

参照元の表に計算用の列をいくつも用意すればそれも実現できるのだが、保守性の面でそれはやりたくなかった。
そういうとき使える方法をその時頑張ってネットで検索したのだが、いい方法が見つからず・・・
結局自分なりにやり方を見つけたので、忘れないようここに残しておく。

概要

例として上記のような表が存在するとする。
各クラスの月ごとの日直担当を管理している表

この日直管理表から、↑の結果表のK4セルのように
・クラス：２－１
・日付：2023/2/1
・性別：女
の日直の名前を抽出したい場合、以下のような計算式となる。


{=INDEX($F$9:$F$33,MATCH(1,($B$9:$B$33=$H4)*($C$9:$C$33<=$I4)*($D$9:$D$33>=$I4)*($E$9:$E$33=$J4),0),1)}

計算式を要約するとこんな感じ。


{=INDEX( 日直名列 
,MATCH(1,( クラス列 
= "２－１" 
)*( 開始日列 
<= "2023/2/1" 
)* (終了日列 
>= "2023/2/1" 
)*( 性別列 
= "女" 
),0),1)}

最初と後ろに {} がついているのは、 配列数式 という方法を使っているため。
（配列数式がわからない人は、他のサイトで先に調べたほうがいいかもしれません。）

◆注意
配列数式を使っているため、この計算式をセルに入力後は、
必ず Ctrl+Shift+Enterキー で確定する必要があります。
※：ただしExcel2019以降のバージョンなら、このキーで確定する必要はなし

このExcel計算式は、大きく３つに分けられる。

①：
 ($B$9:$B$33=$H4)*($C$9:$C$33<=$I4)*($D$9:$D$33>=$I4)*($E$9:$E$33=$J4)

②：
 MATCH(1,【①の結果】,0)

③：
 =INDEX($F$9:$F$33,【②の結果】,1)

①の計算式は、表内を検索するときの条件
②の計算式は、①と③の計算式を繋ぐためのもの
③の計算式は、最終的にセルに表示したい値の範囲を設定する

ちなみに


=INDEX( $F:$F 
,MATCH(1,( $B:$B 
=$H4)*( $C:$C 
<=$I4)*( $D:$D 
>=$I4)*( $E:$E 
=$J4),0),1)

のように列ごと範囲を指定しても、（このエクセルの場合）結果は同じとなる。

逆に


=INDEX( $F$9 
: $F$33 
,MATCH(1,(  $B$10 
 
: $B$33 
=$H4)*( $C$9 
:   $C$32 
 
 
<=$I4)*( $D$9 
: $D$33 
>=$I4)*( $E$9 
: $E$33 
=$J4),0),1)

↑の 赤字の箇所 のように、各箇所で指定している範囲の高さが異なると、結果がおかしくなるため注意。

列ごと範囲を指定してもなぜうまくいくのか、
各箇所で範囲の高さが異なるとなぜおかしくなるのかは、この後の説明を見ればわかる・・・はず！

①の計算式


($B$9:$B$33=$H4)*($C$9:$C$33<=$I4)*($D$9:$D$33>=$I4)*($E$9:$E$33=$J4)
 ↓要約 

( クラス列 
= "２－１" 
)*( 開始日列 
<= "2023/2/1" 
)* (終了日列 
>= "2023/2/1" 
)*( 性別列 
= "女" 
)

この計算式はさらに

①-A： $B$9:$B$33=$H4 

　　（B9:B33の範囲内の値が、H4セルの値に一致するか）

①-B： $C$9:$C$33<=$I4 

　　（C9:C33の範囲内の値が、I4セルの値以下か）

①-C： $D$9:$D$33>=$I4 

　　（D9:D33の範囲内の値が、I4セルの値以上か）

①-D： $E$9:$E$33=$J4 

　　（E9:E33の範囲内の値が、J4セルの値に一致するか）

の４つの条件に分けられる。
（抽出したい条件を変えたい場合、これが３つになったり６つになったりする。）

今回の例では、一致/以上/以下のパターンのみ扱っているが、当然

①-X： $B$9:$B$33<>$H4

という 不等号（<>） を使った計算式にすれば、

　　（B9:B33の範囲内の値が、H4セルの値と 不一致 
か）

という条件になる。

この計算式をそれぞれ()で囲んで、それぞれを乗算(*)すると、
指定した全ての条件に一致する場合1を返す
という計算式になる。

なぜ乗算するのか？
乗算するとどうなるのか？
については、

 ・Excelでは「A=B」のような条件式を書いた場合、
　条件が一致していれば「TRUE」、
　条件が不一致であれば「FALSE」を返す

 ・Excelでは計算するとき、
　「TRUE」は「1」として、
　「FALSE」は「0」として扱われる

という２つのルールをわかっていれば、何をしたいのか見えてくる。

①の計算過程を表で説明すると以下のような感じ。

①の計算結果：{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;0;0;0;0;0;0}

②の計算式


MATCH(1,【①の結果】,0)

MATCH関数は、
第2引数で指定した範囲内から、第1引数に一致するセルの相対的な位置を返す。

今回の計算式の場合、
第1引数が1
第2引数が【①の結果】
のため、
配列数式で返ってきた ①の計算結果内 から、
1に一致するセルが何番目なのか を返してくれる。

ちなみに1に一致するセルが複数ある場合、一番最初に見つかったセルを返す。

②の計算過程を表で説明すると以下のような感じ。

②の計算結果：16

③の計算式


=INDEX($F$9:$F$33,【②の結果】,1)
 ↓要約 

=INDEX(日直名列,【②の結果】,1)

INDEX関数は、
第1引数で指定した範囲内から、[第2引数で指定した値]行目のセルの結果を返す。
（第3引数は何列目かを指定するが、今回は1行だけの範囲を指定しているため1固定）

今回の場合、第1引数の範囲が F9:F33 なので、
第2引数が1ならF9の値
第2引数が2ならF10の値
第2引数が3ならF11の値
～～～
第2引数が24ならF32の値
第2引数が25ならF33の値
って感じで返してくれる。

今回の例の場合、【②の結果】が16のため、以下のように 上から16番目のF24の値 を返す。

③の計算結果："花子16代目"

応用編：OR検索したい場合
ここまでは全てAND条件の検索だったが、
ここでさらに OR検索をしたい 場合、以下のような計算式になる。


=INDEX($G$9:$G$33,MATCH(1,IF(
(($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($E$9:$E$33=$K7))
+
(($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($F$9:$F$33=$L7)),1),0),1)

※長いので改行してます

実際の検索例でいうと以下のような感じ。

先に説明したANDだけの検索計算式と異なるのは、


 =INDEX($G$9:$G$33,MATCH(1, 
 IF(
 (($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($E$9:$E$33=$K7)) 

+
 (($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($F$9:$F$33=$L7)) 
,1) 
 ,0),1)

の 太字の部分 。

上の
赤い条件式を【条件A】
青い条件式を【条件B】
だとすると、
【条件A】 or 【条件B】
といった条件になる。

条件同士を()で囲んで+してあげればor条件での検索は完璧！
と言いたいところなのだが・・・
見ての通りこの計算式では２つの条件式の結果を足しているので、
両方の結果がTRUEになる場合、結果が2となってしまい、そのままMATCH関数に渡すとうまく動かない。

なので、
【条件A】＋【条件B】を


 IF( 【条件A】 
＋ 【条件B】 
,1)

のようにIF文で囲ってあげると、
条件の結果を加算した結果が1以上の場合1を返してくれる ようになる。

これはorにしたい条件が増えて


IF( 【条件A】 
+ 【条件B】 
+ 【条件C】 
,1)

となった場合も同様である。

ただ当然のごとく、条件を増やせば増やす程どんどん重くなるので取扱い注意！

デメリット
VLOOKUP関数
HLOOKUP関数
INDEX関数＋MATCH関数
XLOOKUP関数（2020年以降なら）
この方法が上記の方法より明確に劣っている部分を説明する。

デメリット①
「～を含む文字」といったワイルドカード検索ができない
MATCH関数やLOOKUP関数を使った条件指定なら、
「花子*」 って感じで条件を指定してあげれば、 「花子」から始まる文字列 を検索してくれるが、今回の方法ではそれが使えない。

もし
名前が「花子」から始まって、
2020/1/1 ～ 2020/1/31の期間で日直を担当した人
て感じで調べたい場合は、
諦めて計算用の列を別に用意するか、
VBAマクロを使って集計したほうが良いかと思います。

デメリット②
重い遅い
とりあえずこれが１番のデメリット。
データ量が大したことなければサクサク動くが、
この計算式が大量に使われているExcelを開くと重くなるし、計算にも時間がかかる。
もちろんそれは他の計算式にも言えることですが、、、やはりVLOOKUPなどの標準のExcel関数に比べれば重い。

まず今回使っている 配列数式 という仕組みそのものが重い。
今回説明したこれ以外にもいくつか配列数式を使う方法は考えたが、他はさらに重かった。

データ量の多い表に対してこの計算式を使うなら、Excelの自動計算の設定をOFFにするのが賢明でしょう。
もしくはやっぱり、デメリット①と同じくVBAマクロを使うかですね。

この方法は自己流の方法で、正直全然自信が無いため、
「もっといい方法があるよ」
「ここがどういう意味かわからない」
「こういうときに役に立ったよ」
という方がいらっしゃったら是非是非コメント頂けると有り難いですm(_ _)m

お気に入りの記事を「いいね！」で応援しよう