VLOOKUPで、検索先が３シートに分かれていて、番号の大小で検索するとエラーになります。

検索値に１から299までを順不同で入れた時、それぞれ1～99はAシート、100～199はBシート、200～299はCシートから2列目の値を検索したいのですが、図は簡単に作成したもので、こちらはきちんと反映したのですが、実際の業務で使用しているものは2016形式で、式は同様のものですが、ところどころで、FALSE、ブランクになってしまい、困っております。表示形式は何度も確認していますが、昇順でないと検索できないのか、検索値の等号等の式が正しくないのか、複数シートがいけないのか、わかりやすく教えていただけると助かります。よろしくお願いいたします。

No.1 ベストアンサー 回答者： enunokokoro 回答日時： 2023/07/14 20:51

こちら↓の式を応用してみては。

https://excelkowaza.com/vlookup-multi/
Aシートの範囲に検索値がなければBシートを参照するので、数値を
指定する必要もないので。（IFとANDでの設定も不要）
　=IFERROR(IFERROR(IFERROR(VLOOKUP(A2,A!$A$2:$B$102,2,FALSE),VLOOKUP(A2,B!$A$2:$C$102,2,FALSE)),VLOOKUP(A2,'C'!$A$2:$C$102,2,FALSE)),"")

この回答へのお礼

リンク先の説明で、条件で分ける必要がないことに合点がいきました。修正したところ解決できました。大変ありがとうございました。

お礼日時：2023/07/20 16:43

No.6 回答者： goomania 回答日時： 2023/07/16 00:40

No.4,5です。

大変申し訳ありません。No.4の回答にまたまたタイプミスがありました。
冒頭の(1)～(7)の解説のうち、(3)(5)(7)に「A2の値」という文言が登場しますが、「A12の値」の誤りです。文言を置換して解釈願います。

お詫びして、訂正いたします。

No.5 回答者： goomania 回答日時： 2023/07/15 18:29

No.4です。

大変失礼しました。前回投稿の数式は、B12に記述すへき数式とB2に記述すべき数式が混在する数式になっいました。B2セルに記述する数式は

=IFERROR(IFERROR(VLOOKUP(INT($A2*1),INDIRECT(LOOKUP($A2*1,{0,100,200},{"A","B","C"})&"!$A:$B"),2,FALSE),VLOOKUP(""&INT($A2*1),INDIRECT(LOOKUP($A2*1,{0,100,200},{"A","B","C"})&"!$A:$B"),2,FALSE)),"")

となります。
お詫びして訂正いたします。

No.4 回答者： goomania 回答日時： 2023/07/15 18:20

>ところどころで、FALSE、ブランクになってしまい、困っております。

まず、FALSEが返る原因ですが、これは数式に問題があるからです。
ご質問者が掲出された数式は
(1)IFERROR関数で(2)以下の結果がエラーなら「空白」を返す
(2)IF関数でA12<=99が真なら(3)、偽なら(4)
(3)VLOOKUP関数でA2の値からシートAのA列を検索し、一致したらB列の値を返す
(4)IF関数でA12>=100かつA<=199が真なら(5)、偽なら(6)
(5)VLOOKUP関数でA2の値からシートBのA列を検索し、一致したらB列の値を返す
(6)IF関数でA12>=200かつA<=299が真なら(7)
(7)VLOOKUP関数でA2の値からシートCのA列を検索し、一致したらB列の値を返す
という構造になっています。

上記の(6)でお気づきだと思いますが、真の場合の処理があるのに、偽の場合の処理の記述がないので「偽」つまりFALSEがそのまま返ります。
つまり、ご質問者が掲出された数式は、A12が299より大きい数値だったり、文字列だったりした場合、FALSEとなってしまいます。
ご質問者は実際にFALSEとなる場合があると仰っています。299より大きい値の場合、表示を見ればわかると思いますので、検索値が文字列になっている可能性が極めて高いと言えます。

次にブランクとなる場合ですが、ご質問者から何の説明もないので、良く判りませんが、普通に考えると1～99までの連続数だと99行必要です。しかし、VLOOKUPの数式の検索範囲を見ると$A$2:$B$20とか$A$2:$B$22となっており、19行または21行しかありません。このため、もしかするとA2の下20桁が20以上の数値の場合、上記(1)でエラーを検知して空白となっている可能性があります。

あるいは、シートA、シートB、シートCおよび結果表示シートのA列の数値が整数でない場合、例えば10.00001というような値の場合(見かけは10と見えていても)、上記(1)でエラーを検知して空白となっている可能性があります。つまり、シートA、シートB、シートCおよび結果表示シートのA列はどのように入力されているのかが重要です。以下のどちらでしょうか?

(a)A列を手入力している
(b)他の計算結果からをA列を計算により求めている(結果貼付けを含む)

仮に(b)だとすると、小数点以下の端数がある(整数でない)値がないか確認する必要がありますが、EXCELの表示有効桁数は15桁しかないので、端数を検知できない場合があります。

そこで、各シートのC2に

=MOD($A2,1)=0･･･①

という数式を記述し、下方向へコピーします。A列が整数なら、全てTRUEが表示されるはずです。仮に、小数点以下の端数がある(整数でない)値があれば、FALSEが表示されるので見かけは整数だが小数点以下の端数があることが判別できます。もし①でFALSEが返るようなら、VLOOKUP関数「完全一致」で検索しているため、このような状況だと上記(1)でエラーを検知して空白となる可能性があります。

さらに、検索される側、つまり、シートA、シートB、シートCの表のA列に文字列が含まれている可能性があります。

そこで、検索結果表示シートのB2セルに記述する数式を

=IFERROR(IFERROR(VLOOKUP(INT($A2*1),INDIRECT(LOOKUP($A2,{0,100,200},{"A","B","C"})&"!$A:$B"),2,FALSE),VLOOKUP(""&INT($A2*1),INDIRECT(LOOKUP(A12,{0,100,200},{"A","B","C"})&"!$A:$B"),2,FALSE)),"")

という数式に替えて、下方向へコピーしてみてください。

これで、FALSEや、空白が表示されなくなるようなら、原因は上記のとおりです。

この回答へのお礼

詳細に教えていただき、ありがとうございます。ご指摘のとおり、検索値は他シートからドロップダウンリストで、指定するようにしておりました。それを解除しましたら条件を設定せずに処理できるようになりました。どうもありがとうございました。

お礼日時：2023/07/20 16:47

No.3 回答者： mike_g 回答日時： 2023/07/14 21:47

お願い。

シート“管理表”の[数式バー]上に表示されたセル B12 の数式が判然としないので、それをコピーして[補足]欄に貼り付けて、明瞭にして見せてください。
貴方自身で書き写すのはやめてくださいね。必ずコピー＆ペーストでお願いします。

No.2 回答者： chonami 回答日時： 2023/07/14 21:00

検索値の場合分けをしなくても、IFERRORをネストして3シートごとにそれぞれVLOOKUPの式を作って繋いでみればどうでしょう？

1シート目エラーなら2シート目、次に3シート目を探す形にするという感じで。

この回答へのお礼

検索値の場合分けが不要なことについて、ご教示いただきありがとうございました。回答いただきありがとうございました。

お礼日時：2023/07/20 16:50