- +演算子を利用してMATCH関数の検索範囲に複数列を指定した結果を得ることができ、INDEX関数を使った表引きに応用できます。
- LAMBDA関数等を併用する方法もあります。検索範囲の列が多くても式が長くなりません。
+演算子を使用する方法
次の画像では、F3セルに入力されている担当者の名前をB列とC列の両方から検索し、対応する担当地区の値を得ています。
G3セル
=INDEX(D3:D6,MATCH(TRUE,(B3:B6=F3)+(C3:C6=F3)>0))
「(B3:B6=F3)+(C3:C6=F3)>0」の部分により、データの4つの行がそれぞれFALSE（「井上」を含まない行）またはTRUE（「井上」を含む行）の数値に変換されます。
MATCH関数をつかってそこから「TRUE」の位置を取得し、さらにそれをINDEX関数の関数の第2引数とすることで、検索値「井上」を含む行の担当地区の値が得られます。
上記の式中の「(B3:B6=F3)+(C3:C6=F3)>0」の部分だけ実行すると次のようになります。3行目が「TRUE」になっており、MATCH関数の第1引数（検索値）を「TRUE」としている理由がわかると思います。
注意点として、データがこのような形式になっている場合に検索値が「空白セル」でも値を取得してしまうケースが起こりやすいです。
画像では「B地区」を担当する担当者が1人しかおらずC3セルが空白セルになっており、さらにF3セルが空白セルであるため結果的に「B地区」を取得しています。
これを避ける例として、次の画像では「検索値が空白なら何も値を返さない（空文字列を返す）」という式にしています。
G3セル
=IF(F3="","",INDEX(D3:D6,MATCH(TRUE,(B3:B6=F3)+(C3:C6=F3)>0)))
ただし以降ではこの対応部分は省略します。
次の画像では検索する値が縦に3つ並んでいますが、上記の式の「F3」を「F3:F5」に変更しても3つの検索結果を値を取得することはできません（スピルしない）。
そこで次の画像では、絶対参照とフィルコピーを使って3つの担当者の各担当地区を取得しています。
G3セル（下方にコピー）
=INDEX(D$3:D$6,MATCH(TRUE,(B$3:B$6=F3)+(C$3:C$6=F3)>0))
LAMBDA/BYROW/COUNTIF関数を併用する方法
上記の例では検索範囲が2列だったのでまだ式が短く済んでいますが、5列とか10列に増えると長くなり入力が面倒になります。
そこで次の画像では複数列を一括して検索できるようにしています。
I3セル
=INDEX(F3:F6,MATCH(TRUE,BYROW(B3:E6,LAMBDA(a,COUNTIF(a,H3)))>0,0))
BYROW/LAMBDA関数を使って検索範囲（B3:E6）にaと名付け、その1行ごとにCOUNTIF関数を実行しています。これにより各行に検索値（クマ）が含まれているかそれぞれ判定しています（FALSE,FALSE,TRUE,FALSEの4つの値が得られます）。MATCH関数によりそこからTRUEの位置を取得し、さらにINDEX関数でTRUEに対応する行の「分類」の値を取得しています。
ただしこの場合も、検索値のスピルはできません。
そこで次の画像では、絶対参照とフィルコピーを使用して、3つの名称に対応する分類を取得しています。
I3セル
=INDEX(F$3:F$6,MATCH(TRUE,BYROW(B$3:E$6,LAMBDA(a,COUNTIF(a,H3)))>0,0))
備考
XLOOKUP関数で全く同じことをする例を次の記事で紹介しています。
特にLAMBDA関数を使うケースでは式は長くなるため、比較的簡潔に済むXLOOKUP関数の方をおすすめします。
