エクセル関数で検索能力がある関数を教えてください。

質問

「シート１」と「シート２」があります。
「シート１」には

　　１　　　　　　２
Ａ　通信費　　課税仕入
Ｂ　借入金　　対象外
Ｃ　支払利息　非課税仕入

のような表があります。

「シート２」には
　　１　　　　　２
Ａ　通信費
Ｂ　交際費

となっていたとします。

この場合、「シート２」のセルＡ２に、
ＶＬＯＯＫＵＰ関数で「シート１」を参照し、「課税仕入」を表示させることはできるのですが、
「シート２」のセルＢ２にセルＡ２の関数をコピーした場合、
「シート２」のセルＢ１にセルに表示されている「交際費」は「シート１」の表には登録していないため、セルＢ２にはエラー値が表示されてしまいます。

「シート１」の表に「交際費」を登録すればいいのですが、
自己都合上、「シート２」のセルＢ２に、
「シート２」セルＢ１の項目が「シート１」の「１列目」にあれば、「２列目」の項目を表示する。
「シート１」の「１列目」に該当する項目が無ければ、「シート２」のセルＢ２には「対象外」と表示するようにしたいのです。

言い換えますと、「シート２」の通信費は「シート１」にあるのでＶＬＯＯＫＵＰ関数で課税仕入を導くことが出来ます。
しかし、「シート２」の交際費は「シート１」に無いため、ＶＬＯＯＫＵＰ関数をしようできません。
「シート１」に無い項目の場合には、「シート２」に「対象外」と表示されるような関数の式が知りたいです。

わかりにくい文章になってしまいましたが、よろしくお願いします。

回答No.1

mysticage · ベストアンサー

=IF(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,0))=TRUE,"対象外",VLOOKUP(A1,Sheet1!A:B,2,0))

こういう式を、シート２のA2にいれて、B2にもコピーしてください

IF と　ISERROR関数を組み合わせています。

もし、VLOOKの式での返り値が、エラーだったならば、対象外を表示せよ、そうでないなら、VLOOKの式で帰ってくる値をそのまま表示していいよ　という式になっています

お礼

返事が遅れてしまいまして申し訳ありません。
問題は解決しました！
ありがとうございました。

回答No.5

imogasi

質問表現に付いて
(1)
　　１　　　　　　２
Ａ　通信費　　課税仕入
Ｂ　借入金　　対象外
Ｃ　支払利息　非課税仕入
はシートを移したものなら
　　A　　　　B　　　C
１　通信費　借入金
２　課税仕入
のようにシートと同じ行と列の表現してください。
これを守らないと、読みにくい。
（２）質問の表現が長いのですが
VLOOKUPで検索表に見つからないアイテムの場合、「対象外」と
出したい、ということではないのでしょうか？
A1:B5
コード 内容
1 a
2 b
3 c
4 d

D３：E5
3 c
6 対象外
1 a
E3の式
IF(ISERROR(VLOOKUP(D3,$A$2:$B$5,2,FALSE)),"対象外",VLOOKUP(D3,$A$2:$B$5,2,FALSE))
D4以下に式を複写。

お礼

ご指摘ありがとうございます。
（１）質問表現について
（２）質問が長いことについて
おっしゃるとおりです。
シートを写したつもりだったんですが、「ＡＢＣ」と「１２３」の場所を逆にしてしまいました。
すいません。

文章わかりづらくなってしまったことをお詫びいたします。

回答No.4

Nouble

ご質問の内容が
「詮索機能を実現するにはどのような構文がありえるのか網羅したい」
と言うこととお見受けしましたので
愚考して式を作ってみました…　(*^_^*)

この１件だけ出せばいいという内容からすると
VLOOKUPに比べやや邪道ですが

=CHOOSE(SIGN(SUMPRODUCT((Sheet1!$A$1:$A$3=$A1)*1)-1)+2,"対象外",INDEX(Sheet1!$A$1:$B$3,MATCH($A1,Sheet1!$A$1:$A$3,0),2),OFFSET(Sheet1!$A$1,LOOKUP(1,{1},SMALL((Sheet1!$A$1:$A$3<>$A1)*65555+ROW(Sheet1!$A$1:$A$2),COLUMN(Sheet1!A1)))-1,1,1,1))

=CHOOSE(SUMPRODUCT((Sheet1!$A$1:$A$3=$A1)*ROW(Sheet1!$A$1:$A$3))+1,"対象外",Sheet1!$B$1,Sheet1!$B$2,Sheet1!$B$3)

=IF(COUNTIF(Sheet1!A:A,$A1)<>1,"対象外",OFFSET(Sheet1!$A$1,LOOKUP(1,{1},SMALL((Sheet1!$A$1:$A$3<>$A1)*65555+ROW(Sheet1!$A$1:$A$3),COLUMN(Sheet1!A1)))-1,1,1,1))

最初のは長いですが
CHOOSE構文のインデックスの算出法とか
OFFSET構文を使っているあたりとか
検索に２件以上ヒットした場合にも右に式をコピーすれば２件目以降の値表示をするとか
SMALL構文が扱う配列の作り方とか
etc
自画自賛になりますが秀逸なものがあると思います

また、元データの位置を１つ下にずらして頂いて
新たに空いたB1に「対象Guy」と入れて頂ければ

=LOOKUP(INDEX(LARGE((Sheet6!$A$2:$A$4=$A1)*ROW(Sheet6!$A$1:$A$3),COLUMN(Sheet6!A1))+1,),ROW(Sheet6!$A$1:$A$4),Sheet6!$B$1:$B$4)
とか

{=INDEX(Sheet6!$A$1:$B$4,LARGE((Sheet6!$A$2:$A$4=$A1)*ROW(Sheet6!$A$1:$A$3),COLUMN(Sheet6!A1))+1,2)}
とかも使えますね

最後に、DGET構文やHLOOKUP構文が使えるとなかなかなのですが
DGET構文は検出条件の指定に
・見出しと検出キーが縦並びにセットになっていないといけない
・定数配列で指定できない

HLOOKUP構文はデータの元フィールドが横向きに並んでいないといけない

などの癖があって、ちょっと惜しいのですが使えません

お礼

色々とありがとうございます。
使ったことが無い関数目白押しです・・
これから実験させていただきます。
ありがとうございました。

回答No.3

maron--5

ANo.2のmaronです
◆ほかに、こんな方法もありますよ
=IF(A1="","",IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),VLOOKUP(A1,Sheet1!A:B,2,0),"対象外"))

回答No.2

maron--5

◆こんな方法もありますよ
◆Sheet1
　　A　　　　　　　B
1　通信費　　　課税仕入
2　借入金　　　対象外
3　支払利息　 非課税仕入

◆Sheet2
　　A　　　　　　　B
1　通信費
2　交際費
3

◆Sheet2のB1の式
B1=IF(A1="","",IF(COUNTIF(Sheet1!A:A,Sheet2!A1),VLOOKUP(A1,Sheet1!A:B,2,0),"対象外"))
★下にコピー

お礼

返事が遅れまして申し訳ありません。
このような方法もあるんですか。
先ほど試してみました。
ありがとうございました。