[[20190228134041]]　『VLOOKUP？』（あんぱん）　ページの最後に飛ぶ

『VLOOKUP？』（あんぱん）

excelにてシフト表を作成しています。
sheet1に下記のような名簿があります。

A列（氏名）　　B列（区分）　C列（氏名）　　　D列（区分）
田中　太郎　　　山　　　　　佐藤　太郎　　　　川
田中　次郎　　　川　　　　　佐藤　花子　　　　山
田中　花子　　　山　　　　　佐藤　よしこ　　　山

sheet2には下記のような当日の利用表を作成しています。

A列（氏名）　B列（時間）　C列（区分）

A列に氏名が打ち込まれたときに、C列に区分が自動入力されるように関数をくみたいと思いますが、やり方がわかりません。
VLOOKUP関数でやると1行目の田中　太郎の区分は「山」としっかりと出るのですが、2行目に佐藤　花子と打ち込むと「＃N/A」と出てしまいます。（数式を入れ替えても）

助言をお願いします。

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞

 Sheet1をA列、B列だけにすることはできないのだろうか？
（ねむねむ） 2019/02/28(木) 13:55

 できない場合は
 =IF(OR(COUNTIF(Sheet1!A:A,A2),COUNTIF(Sheet1!C:C,A2)),VLOOKUP(A2,IF(COUNTIF(Sheet1!A:A,A2),Sheet1!A:B,Sheet1!C:D),2,FALSE),"")
 ではどうだろうか？
（ねむねむ） 2019/02/28(木) 14:06

C1 =IFERROR(IFERROR(VLOOKUP(A1,Sheet1!$A:$B,2,FALSE),VLOOKUP(A1,Sheet1!$C:$D,2,FALSE)),"データ無し")

2007であればiferrorを使った対処療法的ではあるがこれで出るだろうか
該当が無ければデータ無しと表示されるようにしてあるので、そこは自由に変えて欲しい
（名無） 2019/02/28(木) 14:07

 おっと
 =IF(COUNTIF(Sheet1!A:C,A2),VLOOKUP(A2,IF(COUNTIF(Sheet1!A:A,A2),Sheet1!A:B,Sheet1!C:D),2,FALSE),"")
 でも構わないか。
（ねむねむ） 2019/02/28(木) 14:10

 書いているうちに他の回答が付きましたがそのまま載せます。
 Sheet2のC2:=IFERROR(IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),VLOOKUP(A2,Sheet1!C:D,2,FALSE)),"")
 下方向へコピー

 ちなみにこれ
[[20190226183048]]『関数について』（アンパン）
 の続きですよね？次回から同じようなケースがあった場合は新たにトピを作らず前のトピに書いてください。
（bi） 2019/02/28(木) 14:12

 =IFERROR(VLOOKUP(A2,IF(COUNTIF(Sheet1!A:A,A2),Sheet1!A:B,Sheet1!C:D),2,FALSE),"")
 でよかった。
（ねむねむ） 2019/02/28(木) 14:54

ねむねむさん、名無さん、biさんありがとうございました。
biさん
ご指摘の通り一緒です。
今後気をつけます。
なんとか自分のやりたい方向に向かいました。有難うございました。
（あんぱん） 2019/02/28(木) 17:34

すみません。
もう一度質問です。
上記に例に出しましたが、sheet1の名簿はグループごとに分かれており、例には2グループしか書いておりませんが、本来は11グループあります。なので最終グループはU列に氏名、V列に区分があります。
書いて頂いた数式　=IFERROR(IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),VLOOKUP(A2,Sheet1!C:D,2,FALSE)),"")
を用いていますが、その場合はどのように数式を増やしていけばよいのでしょうか？

（あんぱん） 2019/02/28(木) 18:40

 =IF(COUNTIF(Sheet1!A$2:V$4,A1)=0,"",INDEX(Sheet1!A$2:V$4,SUMPRODUCT((Sheet1!A$2:V$4=A1)*ROW(A$1:V$3)),SUMPRODUCT((Sheet1!A$2:V$4=A1)*COLUMN(A$1:V$3))+1))

 A$2:V$4の部分は実際の範囲に変えてください。
 ROW(A$1:V$3)の部分は例えばデータ範囲がA$2:V$101だったらROW(A$1:V$100)に変えてください。
（bi） 2019/03/01(金) 09:39

ありがとうございました！
なんとかできました！
（あんぱん） 2019/03/01(金) 13:40

すみません！
早急に教えていただきたいのですが、上記の表でbiさんの数式をそのまま打ち込んだのですが、データ範囲がA3:W40に変えると何故か最後の12グループだけ区分が反映されず、空白になってしまいます。
数式は

=IF(COUNTIF(Sheet1!A$3:W$40,A1)=0,"",INDEX(Sheet1!A$3:W$40,SUMPRODUCT((Sheet1!A$3:W$40=A1)*ROW(A$2:W$39)),SUMPRODUCT((Sheet1!A$3:W$40=A1)*COLUMN(A$2:W$39))+1))

としています。
ただ質問する身として誠に申し訳ないのですがよくわからず助けていただきたいです。
宜しくお願いします。

（あんぱん） 2019/03/31(日) 00:13

(1)
＞ROW(A$1:V$3)の部分は例えばデータ範囲がA$2:V$101だったらROW(A$1:V$100)に変えてください。
とあるように、
ROW(A$1:V$100)の部分は、
1から始まって100（行数と同じ）となるような数列にする必要があります。
そのことをよく理解してください。
そうすれば自ずとどう修正すればよいかわかるはずです。
結果だけでなく仕組みを理解する必要があります。
　
(2)
A列から始まって２つずつ列を使うのだから、
終わりは偶数列でなければならない。
W列は23列目だから奇数の列です。
何か勘違いがあるのでは？

（γ） 2019/03/31(日) 11:53

例えばデータ範囲がA$2:V$101だったらROW($1:$100)に

ROWは行番号取り出よって列を表現するアルファベットは省略できます

COLUMN(A$2:W$39)はCOLUMN($A2:$W2)に

COLUMNは列番号を取り出すよって行番号にこだわりなし

$A2:$W2　　A　W　列固定だから＄を前に

(Sheet1!A$3:W$40=A1)*ROW(A$2:W$39)→(Sheet1!$A$3:$W$40=A1)*ROW($2:$39)

は何行目を検索　　ただしA1の値が表の中に1個しかない場合

(Sheet1!A$3:W$40=A1)*COLUMN(A$2:W$39))→(Sheet1!$A$3:$W$40=A1)*COLUMN($A2:$W2))

は何列目か

A列　氏名　B列　区分　C列　氏名　D列　区分　E列　氏名　F列　区分　G列　氏名　D列　区分・・・・・
と横にひろがった表の場合にいいかも

（han） 2019/04/06(土) 16:31

(Sheet1!$A$3:$W$40=A1)*COLUMN($A2:$W2))

Sheet1!$A$3:$W$40　と　COLUMN($A2:$W2))　の列数は同じに

(Sheet1!$A$3:$W$40=A1)*ROW($2:$39) 　の行すうも同じに
（han） 2019/04/06(土) 16:37

コメント返信：

[ 一覧(最新更新順) ]

[[20190228134041]] 『VLOOKUP？』（あんぱん） ページの最後に飛ぶ

[[20190228134041]]　『VLOOKUP？』（あんぱん）　ページの最後に飛ぶ