ただし複雑なうえ制約も大きくなり実用性は低いので、FILTER関数を使用することを強くお勧めします。
取得例
次の画像ではF3セルに入力された県名をB列から検索し、対応する市の名前を取得しています。
G3セルの式は次のようなものです。
G3セル
=IFERROR(INDEX(C$3:C$10, SMALL(IFNA(MATCH($F$3&ROW(B$3:B$10),$B$3:$B$10&ROW(B$3:B$10),0),""),ROWS(G$3:G3)) ),"")
この式をH10セルまで下・右方向にフィルコピーすると次のようになります。
該当する3つの行の2つの列の値を取得できている（4行目以降は空文字列）のがわかります。
補足ですが、式中の「=$F$3&ROW(B$3:B$10)」と「=$B$3:$B$10&ROW(B$3:B$10)」の部分をそれぞれ実行すると次のようになります。
前者は検索値「滋賀県」を検索範囲の列番号と連結したもの、後者は抽出範囲を列番号と連結したものです。
MATCH関数を使って後者の範囲から前者のそれぞれの位置を検索すると……
次のように、検索値が存在する（検索範囲B3:B10内における）行の位置が取得できます（IFNA関数を併用してエラーを空文字列に置き換えています）。
この結果に対しSMALL関数を適用し、値が小さい順に隙なく並べたうえでINDEX関数の第2引数（n番目）とすれば、2番目の画像のような結果になるというわけです。
最新の関数は使っていないのである程度古いバージョンでも動きますがスピルが使えないとうまくいきません。
以下は備考です。
- 「ROWS(G$3:G3)」の部分を「2」にすれば2番目の該当（上記例では「東近江市」「112」）を取得でき、3以上についても同様です。なお、この部分は下にフィルするにつれて1,2,3と増えていく連番を生成するための部分です。「ROW(A1)」とした方が短くなりますが、無関係のセルを参照するのを嫌ってこうしています。
- 検索値を行番号と連結したうえで検索しているため、検索値が数字である場合に誤った検索をする恐れがあります。
- 同じ理由によりワイルドカードがうまく機能しなくなります。完全一致検索に割り切って使うこととなります。
FILTER関数を使う方法
次の画像では、FILTER関数を使うことにより1つの式で上記の2番目の画像と同じ結果を得ています。
ただし関数により表示されているのは3行目までで、4行目以降に関数の効果は及んでいません。
G3セル
=FILTER(C3:D10,B3:B10=F3)
第1引数で抽出範囲を、第2引数で条件式（条件範囲と値の組み合わせ）を指定することで値を一括して取得できます。
INEDEX/MATCHに限らずVLOOKUP関数やXLOOKUP関数においても「条件に該当する値をすべて抽出したい」というニーズは確かにあります。しかし最初の例に限らず、作業列や面倒な絶対参照の設定が必要になるなどいずれも複雑なものになるので、FILTER関数を使用することをおすすめします。
