ExcelのVLOOKUPを使った検索について＋α

質問

またまた質問させてもらいます・・・

VLOOKUPは本来検索範囲の左端の列を検索していくの
ですが、それを他の列をキーにしたいのです。ですが、
元表の順番を変えることができないので、検索できずに
困っています。そこで質問なんですが、

・元表を変えずに（コピー等を作らずに）、関数内だけで
表を擬似的に並べ替えて、左端の列以外の列を検索キーと
した列検索は可能でしょうか？可能なら方法はどうやれば
よろしいでしょうか。

・もしそれがだめな場合他のアプローチはありますが？
できれば方法も・・・

あと、もうひとつ質問なんですが、検索範囲を指定する
時に左上のセルの位置は固定で右下のセルが表の行数
によって変化する場合、汎用的に関数で指定することは可
能でしょうか？

つまり、B4:H14という範囲指定がある場合、表の行数がひ
とつ増えるとB4:H15と範囲指定を変えてくれるような方法
はありますか？ということです。

ということです。それではよろしくお願いします。

回答No.3

nishi6 · ベストアンサー

例えば、A1の値をG4:G15と照合して、一致したら対応するE列の値を持ってきます。
　=IF(ISERROR(MATCH(A1,G4:G15,0)),"",INDEX(E4:E15,MATCH(A1,G4:G15,0)))
ISERRORは見つからなかった場合の対応です。

範囲が変動する場合は、例えばD列は通番とかでデータの最後まで埋められている(空白が無い)とすれば、
　=INDEX(INDIRECT("E4:E"&(COUNTA(D:D)+3)),MATCH(A1,INDIRECT("G4:G"&(COUNTA(D:D))+3),0))
でできるはずです。
見つからなかった場合の対応は最初の式のようにして
　ISERROR(MATCH(A1,INDIRECT("G4:G"&(COUNTA(D:D)+3)),0))
と変形します。

ご参考に。

お礼

ありがとうございました、この方法が一番うまくできました。ありがとうございます。

回答No.2

gonta_goma

LOOKUP関数を使用されたらどうでしょうか。
=LOOKUP(検査値,検査範囲,対応範囲)という具合になります。
検査キーがB1:B15、C1の値で検索し、A列の値を取り出す場合は、
=LOOKUP(C1,B1:B15,A1:A15)になります。

＞変化する場合、汎用的に関数で指定することは可能でしょうか？

何行まで値が入っているかを自分で指定する方法、例えば15行までデータが入っているのでD1セルに15と入力する方法で良ければ、
=LOOKUP(C1,B1:INDIRECT("B"&D1),A1:INDIRECT("A"&D1))
とかいう方法があると思います。

表の行数を関数で求めるのなら、D1セルに配列数式で、
{=MAX(IF(B1:B65535<>"",ROW(B1:B65535)))}
を入力しておく方法もあるかと思います。

回答No.1

k_eba

＞・元表を変えずに
　元表をｖｌｏｏｐｕｐで使用しやすいように
　違うシートにコピーしてお使い下さい
＞B4:H14という範囲指定がある場合、表の行数がひ
　ＩＮＤＥＸ関数をお使い下さい。

　使用方法はヘルプでＩＮＤＥＸと入力し検索して下さい

がんばって下さい

補足

「コピー」とかは無しでと書いたつもりなのですが・・・（つまり違うシートにコピーを作らずにという事です。）
それがあればやっていますし。自分でもわかります。それ以外の方法が無いかということです。