Excelの関数で、セルの位置情報を出したい

質問

VLOOKUP HLOOKUPなどで数値を検索して出したあと、
その数値がどのセルに入っているか表示させる関数は組み立てられるでしょうか？
検索範囲内の端から、１、２。。。って表示されるタイプではなく、
A10とかC５とかで出したいです。

さらに、位置を出したセルから、ひとつ下へ下がったセルの数値を参照したい、ということは出来ませんか？

VBAは全く分かりません。
関数で出来ないなら諦めます。。。

回答No.2

mshr1962 · ベストアンサー

VLOOKUPの例で端から1,2はINDEX関数で出来るのはわかりますよね。
アドレスにする場合は
範囲がA1:A100の場合
=ADDRESS(INDEX(A1:A100,MATCH(検索値,A1:A100,0),1),1,4)
範囲がA100:A200の場合
=ADDRESS(INDEX(A100:A200,MATCH(検索値,A100:A200,0),1)+99,1,4)

＞さらに、位置を出したセルから、ひとつ下へ下がったセルの数値を参照したい、ということは出来ませんか？
単純に+1で出来ます
範囲がA1:A100の場合
=ADDRESS(INDEX(A1:A100,MATCH(検索値,A1:A100,0),1)+1,1,4)
範囲がA100:A200の場合
=ADDRESS(INDEX(A100:A200,MATCH(検索値,A100:A200,0),1)+100,1,4)

HLOOKUPの場合は下記の様になります。
範囲がA1:AA1の場合
=ADDRESS(1,INDEX(A1:AA1,MATCH(検索値,A1:AA1,0),1),4)
範囲がAA1:BA1の場合
=ADDRESS(1,INDEX(AA1:BA1,MATCH(検索値,AA1:BA1,0),1)+26,4)

お礼

仕事で困っていたので、こんなにすぐ回答が集まってとてもうれしかったです。

この回答で試してみて、うまくいかず、少し修正して望み通りの結果になりました。
よって、これをベストアンサーにさせていただきます。
皆様ありがとうございました。

回答No.7

KURUMITO

例えばＡ列からＣ列にかけてデータが入力されており、Ｅ1セルにA列で検索するためのデータを入力し、Ｄ1セルには検索した結果でＣ列のデータをＦ1セルに表示させるとしたらＦ１セルには次の式を入力しますね。

=IF(E1="","",VLOOKUP(E1,A:C,3,FALSE))

そこでＦ1セルに表示されているセル番地をＧ1セルに表示させるとしたらＧ1セルには次の式を入力します。

=IF(E1="","","C"&MATCH(E1,A:A,0))

Ｇ1セルの一つ下行のセルの値をＨ1セルに表示させるとしたらＨ1セルには次の式を入力します。

=IF(G1="","",INDIRECT(LEFT(G1,1)&MID(G1,2,10)+1))

回答No.6

keithin

たとえば
=VLOOKUP(A1,D1:G5,3,FALSE)
といった式があったとすると，キーになるのは
　検索値である「A1」
　対象範囲の「G1:G5」
　その中から実際に取ってきたい「３」列目
の３つの情報です。

この３つを使って式を組んでみると
=ADDRESS(MATCH(A1,INDEX(D1:G5,,1),0),COLUMN(D1:G5)-1+3,4)
とか
=CELL("address",INDEX(D1:G5,MATCH(A1,INDEX(D1:G5,,1),0),3))
=SUBSTITUTE(CELL("address",INDEX(D1:G5,MATCH(A1,INDEX(D1:G5,,1),0),3)),"$","")
といった組み立てで，セル番地は計算できます。

しかし
＞位置を出したセルから、ひとつ下へ下がったセルの数値を参照したい

位置を出す為にわざわざセル番地を求めたいと思ったのなら，そんな事をする必要は全くありません。

たとえば上述式でも縦方向はいずれもMATCH関数で割り出しているので，その＋１すれば
=INDEX(D1:G5,MATCH(A1,INDEX(D1:G5,,1),0)+1,3)

あるいはこの場合はそもそも「具体的に何列のデータが欲しいのか」は最初から判っているのですから
=INDEX(F1:F5,MATCH(A1,D1:D5,0)+1)
で十分です。

回答No.5

aokii

A10とかC５とかで出すのは難しいです。

C列に検索される数値が入っているとして、その数値がどの行に入っているか表示させる関数は、
=MATCH(4,C:C,0)

ちなみに、
行は、=ROW(B1)
列は、=COLUMN(B1)
で出せます。

位置を出したセルから、ひとつ下へ下がったセルの数値を参照したい場合は、OFFSET関数はいかがでしょう。
位置を出したセルをC1とすると、
=OFFSET(C1,1,0)
でひとつ下へ下がったセルの数値を参照できます。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＞その数値がどのセルに入っているか表示させる関数は組み立てられるでしょうか？
=CELL("address",INDEX(B4:B9,MATCH(C6,A4:A9,0)))
=SUBSTITUTE(CELL("address",INDEX(B4:B9,MATCH(C6,A4:A9,0))),"$","")
=SUBSTITUTE(CELL("address",INDEX(B4:B9,MATCH(C6,A4:A9,0))),"$","",1)
=SUBSTITUTE(CELL("address",INDEX(B4:B9,MATCH(C6,A4:A9,0))),"$","",2)
ただ、アドレスを返しても活用方法が考えられません。

＞さらに、位置を出したセルから、ひとつ下へ下がったセルの数値を参照したい
=INDEX(B4:B9,MATCH(C6,A4:A9,0)+1)
とか
=INDEX(B5:B10,MATCH(C6,A4:A9,0))

回答No.3

m6324m

まず、範囲名を付けます。
・VLOOKUPで検索する範囲に『データ』
・VLOOKUPで検索する範囲の一番左上に『sデータ』

VLOOKUPは下記のように、F2の値を範囲データの一番左の列で照合し、3番目の列を持ってくる例です。
　　=VLOOKUP(F2,データ,3,FALSE)

一例として、この抽出されたセルのAddressを、

　　=IF(ISERROR(VLOOKUP(F2,データ,3,FALSE)),"",ADDRESS(MATCH(F2,sデータ)+CELL("row",データ)-1,CELL("col",データ)+3-1,4))

のようにして計算式で、セルの位置情報が出ます。
MATCH関数で何行目か検索し、検索範囲の位置を補正して表示しています。

回答No.1

oct1290

情報が少なく質問の内容があまり理解できませんが
http://ohpa.net/modules/xlnote/content0174.htmlが参考になりますか？