ある範囲の中で指定の文字列が該当する番号を求めるには

いつもお世話になります。
OSは、Win10 エクセルは、2016　です。

sheet"一覧"に参照図のように全角カタカナのア～ンがあります。

例えば
別sheet"B2" = ガ　021
別sheet"C2" = ハ　081

上記の、021　081　を求める数式を御教授いただけませせんか。

質問者からの補足コメント

• 

  早速のご回答を有難うございます。
  
  "検索文字"のところで試させていただきました。
  中々うまくゆきません。
  誠に申し訳ありませんが再指導いただけませんでしょうか。
  
  
  大変失礼いたしました。
  ご不明の
  検索する文字がどこにあって、数式をどのシートに記入するのかはっきりしませんので
  
  ガ = 顧客名簿Sheet1)B2 数式の入力セル = 顧客名簿Sheet1)B3
  ハ = 顧客名簿Sheet1)C2 数式の入力セル = 顧客名簿Sheet1)C3
  
  因みに　ア～ンのカタカナの一覧は
  
  　一覧(Sheetc2) です。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/12/04 17:04

No.2 ベストアンサー 回答者： fujillin 回答日時： 2021/12/04 18:16

No1です。

＞中々うまくゆきません。
式を入力しているのが、別のシートのようですので、式中のセルやセル範囲の参照には全てシート名を付す必要があります。
そのあたりではないでしょうか？

こちらで再確認したところ、SUMPRODUCT関数だと、どうも挙動がおかしなところがありそうなので、もう少し厳密に検索できるようにロジックを変えてみました。

＞因みに　ア～ンのカタカナの一覧は
＞　一覧(Sheetc2) です。
えぇ～っと・・
シート名は「一覧」でいいんですよね？
もしも、シート名が「一覧(Sheet2)」の場合は、以下の式中の「一覧」を全て「一覧(Sheet2)」に変えてください。

数式の入力セルB3に以下の式を入力すれば、変換できるものと思います。
=IF(COUNTIF(一覧!$B$2:$F$16,B2),INDEX(一覧!$A$2:$A$16,AGGREGATE(15,6,ROW(一覧!$B$2:$F$16)/(一覧!$B$2:$F$16=B2),1)-1)&INDEX(一覧!$B$1:$F$1,AGGREGATE(15,6,COLUMN(一覧!$B$2:$F$16)/(一覧!$B$2:$F$16=B2),1)-1),"該当なし")


もしも、式が長いようであれば、「一覧シートの$B$2:$F$16」に名前を付けておくことで、多少は短くすることができます。
仮に、上記の範囲に「表」という名前を付けたとすれば、B3の式は
=IF(COUNTIF(表,B2),INDEX(OFFSET(表,0,-1),AGGREGATE(15,6,ROW(表)/(表=B2),1)-1,1)&INDEX(OFFSET(表,-1,0),1,AGGREGATE(15,6,COLUMN(表)/(表=B2),1)-1),"該当なし")
のようにすることが可能です。

この回答へのお礼

早速の再指導を誠にありがとうございます。

うまくできました。

お礼日時：2021/12/04 18:34

No.1 回答者： fujillin 回答日時： 2021/12/04 16:02

こんにちは

検索する文字がどこにあって、数式をどのシートに記入するのかはっきりしませんので、以下の仮定をしました。

数式は表のあるシートに記入するものと仮定し、検索対象の文字は式中では「検索文字」として表してあります。
（式中に「検索文字」は2か所ありますので、適宜置き換えてください）

=IFERROR(INDEX($A$1:$A$16,SUMPRODUCT(($B$2:$F$16=検索文字)*ROW($B$2:$F$16)))&INDEX($A$1:$F$1,SUMPRODUCT(($B$2:$F$16=検索文字)*COLUMN($B$2:$F$16))),"該当なし")

※ 表内には同じ文字の重複は無いものと仮定しています。
　同じ文字が存在する場合は、上式では正しい結果が得られません。
　（もっと長い式にすれば、同じ文字があっても可能ですけれど・・・）