教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010: 検索してセル位置(行番号、列番号)を返す(SUMPRODUCT,INDEX,OFFSET,ROW,COLUMN)

 

 


 


 

 

データベースを検索してセル位置(行番号と列番号)を返します。

行番号 … SUMPRODUCT((条件)*ROW(範囲))         ★ 合致(True1)、合致しない(False0) × 行番号 = 行番号取得

列番号 … SUMPRODUCT((条件)*COLUMN(範囲) ★ 合致(True1)、合致しない(False0) × 列番号 = 列番号取得

 

  A B
1 さる とり
2 きりん ぞう
3 サイ かめ

 

【ぞう】の位置(●行●列目)を返す

行番号  =SUMPRODUCT((A1:B3="ぞう")*(ROW(A1:B3))) → 2(行目)

列番号  =SUMPRODUCT((A1:B3="ぞう")*(COLUMN(A1:B3))) → 2(列目)

 

※ちなみに単一の列/行からの検索であればMATCH(検索値,B:B,0)で行番号、MATCH(検索値,2:2,0)などで列番号が出ます。


 

◆ 応用例:検索値の下 のセルの値を取り出す

=OFFSET(A1,SUMPRODUCT((A1:B3="ぞう")*ROW(A1:B3)),SUMPRODUCT((A1:B3="ぞう")*(COLUMN(A1:B3)))-1)

又は

=INDEX($A$1:$B$3,SUMPRODUCT((A1:B3=D1)*ROW(A1:B3))+1,SUMPRODUCT((A1:B3=D1)*COLUMN(A1:B3)))

 

◆ 応用例:検索値の下のセルの値を取り出す(H2の兄の名前を参照し、H3に弟の名前を表示)

 

H3に=INDEX(A2:E7,SUMPRODUCT((A2:E7=H2)*ROW(A2:E7)),SUMPRODUCT((A2:E7=H2)*COLUMN(A2:E7)))

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows