VLOOKUP関数 最下行取得方法

タイトルの通りです。
以下関数をシートに埋め込みました。

=IFERROR(VLOOKUP(B10,〇〇!$A$2:$AA$20000,5,0)

〇〇シートのA2-AA20000までデータがある前提で範囲指定していますが、
行数は20000行から変動する場合がある為、X行（最下行）として指定したいです。

上記の場合、関数をどのように書き換えれば良いのでしょうか。
宜しくお願いします。

No.2 ベストアンサー 回答者： tom04 回答日時： 2017/12/21 21:35

こんばんは！

単純に列全体を範囲指定してはダメなのですか？
=IFERROR(VLOOKUP(B10,〇〇!A:AA,5,0)

のように・・・

どうしても最終行までを範囲としたいのであれば、
あまりおススメできる方法ではありませんけど、
=IFERROR(VLOOKUP(B10,INDIRECT("○○!$A$2:$AA$"&MAX(IF(○○!A:A<>"",ROW(A:A)))),5,0),"")

配列数式なのでCtrl＋Shift＋Enterで確定！

これでとりあえずは「○○」シートのA列最終行までがVLOOKUP関数の「範囲」となります。
配列数式はPCにかなりの負担をかけ計算速度が極端に落ちます。

※　後者の配列数式は今回のVLOOKUP関数の使い方としては
まったく無意味のような気がします。m(_ _)m

この回答へのお礼

意図を汲み取っていただきありがとうございました！

お礼日時：2017/12/24 22:37

No.6 回答者： yokomaya 回答日時： 2017/12/23 22:07

名前定義、可変で検索してください。

表の大きさに応じて適切に変化する範囲指定の仕方を
記述する名前定義が多数あります。
それを使った名前で範囲指定するのがセオリーです。

この回答へのお礼

ありがとうございました。

お礼日時：2017/12/24 22:36

No.5 回答者： mike_g 回答日時： 2017/12/22 00:47

》 行数は20000行から変動する

最大で何行まで「変動する」ンですか？
高々1.5倍(3万行)なら、端っから $A$2:$AA$30000 としておくことをお奨めします。
$A:$AA などと約105万行も検索させるのは愚の骨頂かと。

この回答へのお礼

ありがとうございました！

お礼日時：2017/12/24 22:36

No.4 回答者： tom04 回答日時： 2017/12/21 23:05

No.2です。

投稿後思ったのですが、
おそらく下へフィル＆コピーする際にVLOOKUP関数の範囲をずらしたくない！というコトでの質問では？

行全体を範囲としておけば絶対参照にする必要はありません。
もし列方向にフィル＆コピーする場合は、列だけ固定の
=IFERROR(VLOOKUP(B10,〇〇!$A:$AA,5,0)
とします。

※　数式を見るとA列を検索値として5列目（E列）を参照する数式になっていますので、
範囲はE列まで大丈夫ですね。
すなわち
=IFERROR(VLOOKUP(B10,〇〇!A:E,5,0)

で同じ結果になるはずです。m(_ _)m

この回答へのお礼

ご丁寧にありがとうございました！

お礼日時：2017/12/24 22:37

No.3 回答者： d-q-t-p 回答日時： 2017/12/21 22:51

Excel2007以降ならテーブル書式があるんですから 構造化参照に切り替えれば

いいだけだと思います。ついでに VLOOKUPも INDEX/MATCHの組み合わせに
替えた方がいいと思います。
https://qiita.com/tomikiya/items/da8884acdfaa12b …

この回答へのお礼

ありがとうございます

お礼日時：2017/12/24 22:37

No.1 回答者： 5y3u1p0 回答日時： 2017/12/21 21:23

$A$2:$A$20000を$A:$Aにすればよいのでは？

この回答へのお礼

ありがとうございました。参考にさせて頂きました。

お礼日時：2017/12/24 22:38