ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか？

質問

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

回答No.5

noname#52504 · ベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

　=OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))　⇒　A1:F200で値がX1と一致するセルの行番号

＞その「ある範囲」の中には検索したい値が入っているセルは１つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
　ex.　=IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
　【検査範囲】から【検査値】を探し、
　最初にHITしたセルについて、右隣のセルの値を返す。
　ex．　=Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
　For Each セル In 検査範囲
　　If セル = 検査値 Then Exit For
　Next セル
　Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

お礼

ありがとうございました！できました！
VBAをいじり始めてみたのですが、今ひとつだったので大変有り難いです。
関数も使ったことのないものを教えていただき、勉強になりました。
こころから感謝申し上げます。

回答No.4

pbforce

＃1です

やはり、ご質問の内容を、変更することをお勧めします。

ばらばらに並んでいる、現在の元データをVlookupで検索できるように並び替えることを考えたほうが早いかもしれません。

例での氏名とIDで抽出可能な条件はありませんか？
まずは行単位での抽出を考えましょう。

お礼

ありがとうございました。
また教えてください。

回答No.3

imogasi

エクセルでは関数を使ってでは、抜き出しは苦手(式が複雑）です。
この質問コーナーで、私はimogasi方式という作業列を使うやり方でたくさん答えています。Googleで「imogasi方式」で照会すれば、OKWAVEでの同類の質問が相当数でで、そこには私以外の回答も出てきます。参考にしてください。
＞ユーザー定義で作りたいと
やってご覧よ。２つ以上の値を返すユーザー関数って、そうたやすく勉強できないよ。
（A)それよりある列の全セルをIF文で聞いて、該当かどうか調べて、該当なら、好みの列に１行ずつ下へずらして書き出す、を考えた方が格段に易しいよ。
上記（A)と絡めて、条件の第ｘ番目の該当行を帰すユーザー関数を考えたら。

お礼

ありがとうござました。
また教えてください。

回答No.2

hallo-2007

INDEX関数とMATCH関数の組み合わせのことだと思います。

A　　　B
氏名　ID

であった場合
D1にID入れて、E1に
=INDEX(A:A,MATCH(D1,B:B),1)
それぞれの関数の意味はHelpで調べてみてください。
そのほか、色々と使い道のある関数です。

お礼

ありがとうございます。
私が扱っているデータの具体的な例を挙げさせて頂きます。
まさし 00038
ゆみこ 00025
ひろし 00041
たろう 00040
というような形ではなく、

まさし 00038
　　　ゆみこ 00025
　　　　　　ひろし 00041
　欠席　たろう 00040
のように、バラバラに並んでいる形になっています。

関数の意味を見てみたのですが、どちらもA1:A200とかB1:B200という指定はできても、
A1:F200という指定はできないような気がするのです・・・。

氏名やIDがA列やB列にあるとは限らず、
ある人は氏名がC列にあったり、
またある人は氏名がD列にあったりします。
IDがそのすぐ隣にあることは保証されているのですが・・・。
どうぞよろしくお願いいたします。

回答No.1

pbforce

いちばん簡単なのはある範囲をVlookupで検索できるように変更することです。

ある範囲が数列ならそれぞれ名前定義してVlookupで探して、なければ次の名前、なければ次の名前とするのも手かもしれません。

ある範囲の規模や並び方がわかればもう少し有効な策もあるかもしれないですね。

お礼

どうもありがとうございます。
「ある範囲」とは列が６列、行が２００行ぐらいです。
検索したい値がＡ列にあるのか、Ｂ列にあるのか、まったくのランダムで、
Ｂ列にその値がある時も、その隣のＡ列のセルが、空白なのか、何か値が入っているのか、それもランダムになります。
その「ある範囲」の中には検索したい値が入っているセルは１つしかありません。
どうぞよろしくお願いします。