HYPERLINKとADDRESSとMATCHの組み合わせ技

こんばんは。WEB検索で勉強したのですがお助け下さい。

同じブックの２つのSheet間でハイパーリンクを押すとセルがジャンプすると嬉しいです。

■前提
Sheet1は得意先担当者リストをイメージしています。
会社名(A列)と担当者(B列)が並びます。
担当者が複数いる会社もあるので、同じ会社名が並んで表示されるセルもあります。

Sheet2は例えば案件管理リストをイメージしています。
会社名(A列)と案件名や金額や時期が並びます。(画像ではA列のみ表示しています)
A列の会社名の並び順はバラバラで、同じ会社名が何度も登場します。

■実現したいこと
Sheet2のB列にHYPERLINKを入れて、クリックするとSheet1の該当する
会社名(A列)にセルが移動して欲しいです。

※A列には同じ会社名が並ぶことがあるので最初に一致したセルで大丈夫です。
※もし可能ならSheet2の会社名は株式会社や有限会社が抜けていることもあるので
部分一致なら嬉しいです。(完全一致でも大丈夫です)
※Sheet1、Sheet2は実際には「顧客リスト」「案件リスト」という名称にして
いるのでそこも反映した関数の記述方法が分かると嬉しいです。

独学では手に負えなかったのでぜひお力添えください。お待ちしています。

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

• 

  おはようございます。すごいです。出来ました。
  
  =IFERROR(HYPERLINK(ADDRESS(MATCH(D2,顧客!C:C,0),3, 1,TRUE, "[案件リスト.xlsx]顧客"),"ジャンプ"),"")
  
  ※BOOK名とSheet2の名前をつけてみました。
  ※実際はSheet2のD列(企業名)からSheet1のC列(企業名)に飛んでいくので変更してみました。
  ※ハイパーリンクの埋めてあるセルに「ジャンプ」と表示させてみました。
  ※該当がないこともあるのでIFERRORでくくってみました。
  
  完全一致すれば複数の候補があっても飛んでいってくれます。ありがとうございます。
  
  ジャンプ先をSheet1の任意列のセルにコントロールできることは分かったのですが、行全体を選択させることは出来ないでしょうか？欲張り過ぎですね(^^ゞ

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

• 

  =IFERROR(HYPERLINK(ADDRESS(MATCH(D12,◆顧客!B:B,0),13, 1,TRUE, "[顧客LIST.xlsm]◆顧客"),ROW()-11),"無"&CHAR(10)&ROW()-11)
  
  会社名から株式会社を抜く列を増やして参照先にしてみました。
  これで完全一致でもほぼ対応可能になりました。
  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C613,"株式会社",""),"有限会社",""),"合同会社","")
  
  ハイパーリンクの埋め込み箇所もROWを使って通し番号を振っていた列に紛れ込ませてみました。
  なぜかIFERRORを使うとリンク用の下線が消えてしまうので、該当参照先がない場合は「無(文字)」改行「通し番号」という表現で見やすくしてみました。
  
  ジャンプ先の行選択ができれば嬉しいですがもう95点。ありがとうございます！

    補足日時：2018/06/09 14:52

No.1 ベストアンサー 回答者： ママチャリ 回答日時： 2018/06/09 08:25

HYPERLINKとADDRESSとMATCHの組み合わせとなると、こんな感じでしょうか。

Sheet2のB2セルに、次の式を入れて、下へオートフィルしてみて下さい。
式の中にブック名まで記述しないと正しくジャンプしてくれないようなので、ご注意ください。
シート名の変更は、勉強のつもりでご自分で考えて下さいね。

=HYPERLINK(ADDRESS(MATCH(A2,Sheet1!A:A,0),1, 1,TRUE, "[Book1.xlsx]Sheet1"))

この回答へのお礼

アドバイスに従って実際の環境で列選択チャレンジしましたが出来ませんでした。
でも諦めずに練習環境で試して動くことが確認できたので、少しづつ本番のExcelで動くよう修正頑張りました。

◆セル選択の場合
=IFERROR(HYPERLINK(ADDRESS(MATCH(D12,◆顧客!B:B,0),13, 1,TRUE, "[顧客LIST.xlsm]◆顧客"),ROW()-11),"無"&CHAR(10)&ROW()-11)

◆列選択の場合
=IFERROR(HYPERLINK("[顧客LIST.xlsm]◆顧客!"&MATCH(D12,◆顧客!B:B,0)&":"&MATCH(D12,◆顧客!B:B,0),ROW()-11),"無"&CHAR(10)&ROW()-11)

ありがとうございます！絶対に独学では無理でした。
意味が理解できないのですが、無事にGOALしました。ADDRESSはどこいっちゃったの？(笑)

お礼日時：2018/06/09 17:32

No.2 回答者： ママチャリ 回答日時： 2018/06/09 15:11

なんか進化してますね～。

わたしのは進化していませんが、行選択の参考になれば幸いです。

=HYPERLINK("[Book1.xlsx]Sheet1!"&MATCH(A2,Sheet1!A:A,0)&":"&MATCH(A2,Sheet1!A:A,0))