セル範囲から値を探して該当するセルの行番号と列番号を返す数式を紹介&解説します！【エクセルTips】

はじめに
セル範囲の整数に重複が無い（複数一致しない）場合の数式
セル範囲の整数に重複がある場合に左上の行番号、列番号を返す数式
おわりに

はじめに

『Excelで10✕10セルのセル範囲に入っている整数から検索値を探して、見つけたセルの行番号と列番号を返す』
こんな数式ありませんか？というのをTwitterで見かけて数式を考えてみました
1列、1行だと簡単でも、範囲になるといきなり難しくなりますね
この記事ではセル範囲から検索値を探し座標を返す数式について図を使って解説します！

ただしセル範囲に入っている整数に重複がないことなど前提条件がありますので参考にする際には注意してくださいね

セル範囲の整数に重複が無い（複数一致しない）場合の数式

シノ

それでは、早速数式を見ていきましょう！

<例で使用している数式>
検索範囲の整数に重複が無いこと（検索値と一致する整数が複数ないこと）が前提

=SUMPRODUCT((L2=A1:J10)*ROW(A1:J10))&"行目 "&SUMPRODUCT((L2=A1:J10)*COLUMN(A1:J10))&"列目"

キュウ

ちゃんと『38』が入っている『4行目 5列目』が返っていますね！

シノ

ちょっと複雑なので、図を追加して説明していきますね

シノ

まず『(L2=A1:J10)』の部分ですね
セル範囲の各セルに対して、検索値と一致しているかどうかの真偽を返しています
返るのはTRUE（真）かFALSE(偽）です

キュウ

右下の表は0と1に置き換わってますね
FALSEが0、TRUEが1のようです

シノ

TRUEとFALSEは数値に変換するとそれぞれ1と0になります
これは後に説明する行番号、列番号の配列との掛け算の過程で
自動的に変換されているので数式には表れていません

キュウ

これで4行目 5列目の位置に1、それ以外は0でできた
10✕10の配列が得られるんですね

シノ

そのとおりです！
次はこの配列と行番号と列番号の掛け算について見ていきましょう

キュウ

なるほど、4行目 5列目にある1とROW関数が返す行番号4が掛け算されて
4行目 5列目に4、それ以外は0の配列になるんですね

シノ

あとはたくさんの0と4の合計値を
SUMPRODUCTの関数で計算します

キュウ

検索値の入っている行番号の4になる…！

シノ

列番号も同じ流れですね
COLUMN関数を使って列番号を返します

キュウ

今度は列番号なので左から右へと数字が増加していますね

シノ

同じように4行目 5列目の位置に列番号5が入り、
それ以外は0の10✕10の配列が返って合計を計算します

キュウ

あとは＆で文字列結合して好きな表示をさせれば良いのですね
行番号や列番号の配列に0と1の配列を掛け算するところは、面白いですね！

シノ

この数式ではセル範囲に一致するセルが無い場合
『0行目 0列目』が返りますご了承下さいね

シノ

そしてこれも重要なことなのですが
この数式はセル範囲内の整数に重複がある場合…と言うより、
検索値が複数見つかる場合にとんでもない結果が返ってしまいます

キュウ

そうなんですか！？

シノ

行番号と列番号を配列内の数字の合計で出してましたよね
1行目と3行目に検索値と一致する整数が合った場合、
足し算して4行目というデタラメな行番号を返してしまいます

キュウ

使うには前提をしっかり確認する必要があるということですね
気をつけます

セル範囲の整数に重複がある場合に左上の行番号、列番号を返す数式

シノ

重複する場合に一番左上（行を優先）のセルの
行番号、列番号を返す数式を紹介しますね

<例で使用している数式>
重複がある場合に一番左上の行番号と列番号を返す数式

=SUMPRODUCT(SMALL((A1:J10=L2)*ROW(A1:J10),SUM(((A1:J10=L2)*ROW(A1:J10)=0)*1)+1))&"行目 "
&MATCH(L2,OFFSET(A1:J1,SUMPRODUCT(SMALL((A1:J10=L2)*ROW(A1:J10),SUM(((A1:J10=L2)*ROW(A1:J10)=0)*1)+1))-1,0),0)&"列目"

キュウ

これはまた長い数式ですね…