複数の候補列から、検索値と一致するものの隣セルを反映したい

質問

エクセルにて、検索範囲中、複数の列を候補として、検索値と一致するセルの隣のセルを表示させたいのですが、うまくいきません。

そもそもは、ＶＬＯＯＫＵＰでやろうとしたのですが、ＶＬＯＯＫＵＰだと、範囲指定において検索値との一致候補は一番左の列に来ていなければいけないのでしょうか？

ＩＮＤＥＸ，ＭＡＴＣＨも調べたのですが、自力では成功に至りませんでした。

どなたか、ご指導いただけるとありがたく存じます。よろしくお願い致します。

回答No.5

rin01 · ベストアンサー

こんばんは～♪
ふたたび～です。。。。

moon_piyoさんの回答の様に
検索するデータ範囲が、多い場合は
こんな式でも良かな～。。。。

★データ範囲A1:Z10
検索列は、奇数列です。
検索値は、A12へ入力します。

B12: =IF(COUNTIF(A1:Z10,A12),INDEX(B1:Z10,SUMPRODUCT((A1:Z10=A12)*ROW(1:10)),SUMPRODUCT((A1:Z10=A12)*COLUMN(A:Z))),"")

でした。。。。Rin～♪♪

お礼

Rinさん、すごい！

見事にうまくいきました！
しかも、範囲だけ絶対参照にして、
入力欄を下にドラッグしたら、
こちらもうまくいきました！

本当に素晴らしいです。
ありがとうございました！

回答No.4

moon_piyo

こんにちは

A1:AZ4にデータがあって
A5に検索値があるとして

B5: =OFFSET(A1,MAX(INDEX((257*ROW((A1:AZ4))+COLUMN(A1:AZ4))*(A1:AZ4=A5),))/257-1,MOD(MAX(INDEX((257*ROW((A1:AZ4))+COLUMN(A1:AZ4))*(A1:AZ4=A5),)),257))

なかったときなどのエラー処理はなんもかんがえてないです

お礼

MOONさん、ありがとうございます

OFFSETに、COLUMN・・・初めてみる関数ばかりで
とにかく当てはめてみたら、きれいにできました。
すごいなぁと感心しきり。

ただ、私の説明が下手すぎて、
申し訳ないのですが、
この検索は、同一シート上ではなく、
(1)検索シート（マスタ）と、関数を入れるシートを別にして、
(2)更に、VLOOKUPがそうできるように、
数式を縦にドラッグ→コピーして、
数万行あるデータを一気に埋めていくことは、
難しいかもしれません。

私の説明が下手でご迷惑かけました。
もう一度、明日、あらためて、自分の質問を整理して、
投稿し直そうと思います。

今後とも、何卒、よろしくお願い致します。

回答No.3

rin01

こんにちは～♪

こんな式でも、良いですね。。。。

検索値を同じシートのE1に
入力した場合です。

G1: =CHOOSE(COUNTIF(A:A,E1)+(COUNTIF(C:C,E1)*2)+1,"",VLOOKUP(E1,A1:B10,2,0),VLOOKUP(E1,C1:D10,2,0))

。。。Rinでした～♪♪

回答No.2

tinu 2000（@tinu2000）

まず１本目の矢を放ちます。（笑）
ＶＢＡを使わずにシート上で関数のみで処理すると仮定して、

A1～D4までに補足にあったようなデータがあるとします。
検索値はE1にあるとします。

F1に、「=VLOOKUP(E1,A1:B4,2,FALSE)」
G1に、「=IF(ISERROR(F1),VLOOKUP(E1,C1:D4,2,FALSE),F1)」
H1に、「=IF(ISERROR(G1),"",G1)」
で、H1に答えが出ます。
F列、G列は表示しないで隠したら良いですね。

理解しやすく３つのステップで組んで見ました。
IF文をネストして１行で定義する事も可能です。

お礼

TINUさん、第1の矢、ありがとうございます！

VLOOKUPを組み合わせて行く手法なんですね。

ISERROR関数も勉強になりました。

私の例が、単純化しすぎて申し訳なかったのですが、
検索シートが、今回のようにAＢ列とＣＤ列の2列ではなくて、
10何列ぐらい何本もある場合に、
シート全体から、当たりをつける方法はないでしょうか。

よろしければ、ご指導お願い致します。

回答No.1

noname#204879

》 ＶＬＯＯＫＵＰだと、範囲指定において検索値との
》 一致候補は一番左の列に来ていなければいけないの
》 でしょうか？

そのとおり。

MATCH関数と INDEX関数を組み合わせれば“矢でも鉄砲でも持って来い”です。「ご指導」するにはデータの具体例を示す必要があります。

補足

ありがとうございます。

また、説明不足申し訳ありません。

下記のようなシートで、1～4だけでなく、5～８の検査値に関しても、隣のセル（たとえば、5なら、foijrl

を表示させたいです。

<<検索シート＞
　　　　　　Ａ列　　　　Ｂ列　　　　Ｃ列　　　　Ｄ列

１行　　　　1　　　　　　　ajhif 　　　　 5 　　　　 foijrl

２行　　　　　　2 　 　　 fiop 　　　 6 　　　 oairop

３行　　　　　　　3 　 　　 rtoop 　　 　 7 　　 tuwpdu

４行　　　　　　　4 　　　 oitopp 　　　 8 　lkrouyo
よろしくお願い致します。