エクセルで1組1番と入力したら名前が出る関数は？

質問

Ａ1に組，Ｂ1に番号を入力したら，Ｃ1に名前が出るようにしたいんです。
A1に1組1番なら101，2番なら102，3番なら103と入力すれば，VLOOLUP関数でB1に名前を表示できるんですけど，組と番号のセルを作業の関係で分けたいので，やり方がわかりません。

参照用には
組　番　氏名
１　１　たかし
１　２　こうじ
１　３　ひろし
という感じで一覧表はできています。

別の関数の方が簡単でしょうか？

回答No.8

hallo-2007 · ベストアンサー

No7です。すみません、訂正します。
=VLOOKUP(E2,OFFSET(A1,MATCH(D2,A:A,FALSE)-1,1,MATCH(D2,A:A)-MATCH(D2,A:A,FALSE)+1,2),2,FALSE)
では如何でしょうか。
最初の式の場合、番号１の時にNAになりました。

　　Ａ　Ｂ　 Ｃ　Ｄ　 Ｅ　 Ｆ
1 組　番　名　組　番　名
2　 １ １　 た　■　■　□
3 　１　２　こ
4　 １　３　ひ
5　２　１　ま
6　２　２　み
7　３　１　や
で、D2が　2　の場合
=MATCH(D2,A:A,FALSE)　は5(5行目のこと）
=MATCH(D2,A:A)　は6（6行目のこと）が表示されませんでしょうか。

該当者がない場合にエラーがでないですが（お勧めではないですが）
=VLOOKUP(E2,OFFSET(A1,MATCH(D2,A:A,FALSE)-1,1,50,2),2,FALSE)
（1組　最大50名として）ではどうでしょう。

補足

で、D2が　2　の場合
=MATCH(D2,A:A,FALSE)　は5(5行目のこと）
=MATCH(D2,A:A)　は6（6行目のこと）が表示されませんでしょうか。

ここのところは任意のセルに「=MATCH(D2,A:A,FALSE)」の数式を入れて，D2に「２」を入れたら，5行目に色枠がでませんか？という意味ですか？もしこの意味でしたら，5行目には色枠は参照されません。

本当にご丁寧にありがとうございます。

回答No.10

hallo-2007

う～んですね。
A列の組も検索用のD2も数値ですよね、半角ですよね？
どちらかが全角とかではNAですが。
VLOOKUP関数ご存知でしたら
=VLOOKUP(D2,A:C,3,FALSE) とか入れてみて結果はどうでしょうか？

お礼

いろいろと丁寧にありがとうございました！
再度確認したら「標準」になっていたので「数値」にしたらうまくいきました。設定してると思っていたんで…初歩的なことですみませんでした。

回答No.9

hallo-2007

参照は正しいと思います。
>数式をクリックすると，D2のセルに１つとA1の組のところから縦にずっと下まで指定されています。
以下は
>で、D2が　2　の場合
>=MATCH(D2,A:A,FALSE)　は5(5行目のこと）
例題では、式の答えが　5　と表示されませんでしょうか。
MACTH関数の意味ですが、A列を検索範囲として、D2の値（数値の2）を探します。
もし、D2の値があれば、その最初の行番号である　5　となります。
もしなければNAです。

補足

う～ん…
F2に数式を入れた段階で「NA」になります。D2に１，２，３のいずれを入れても変化はありません。
回答の数式をコピーしているのですが…

回答No.7

hallo-2007

No3,No5です。
>Ｄ２に１，Ｅ２に２を入れると，Ｆ２に「たかし」が出るって事ですよね？？
そうなんですけど？
　　Ａ　Ｂ　 Ｃ　Ｄ　 Ｅ　 Ｆ
1 組　番　名　組　番　名
2　 １ １　 た　■　■　□
3 　１　２　こ
4　 １　３　ひ
5　２　１　ま
6　２　２　み
と組が並んでいる必要がありますが。
エラーが出るときは、先に紹介したように関数を分けてみると原因がわかります。
空いているセルに
=MATCH(D2,A:A,FALSE)　で組で指定した最初の行の行番号？
=MATCH(D2,A:A)　で組で指定した最後の行の行番号でますか？
いれてエラーが出ないか確認してみてください。
多分、ここで表示される数値を見ていただくと、式の意味も理解できると思います。

補足

何度もスミマセン。空いているセルに入れてみました。
=MATCH(D2,A:A,FALSE)の「組で指定した最初の行番号」と言うのが何のことか良く分かりませんが，数式をクリックすると，D2のセルに１つとA1の組のところから縦にずっと下まで指定されています。

=MATCH(D2,A:A)　で「組で指定した最後の行の行番号」と言うのも上記同様良く分かりません。
上記と同様の範囲に色枠が示されます。

回答No.6

hige_082

＞A1に1組1番なら101，2番なら102，3番なら103と入力すれば，VLOOLUP関数でB1に名前を表示できるんですけど
これが出来ているのであれば
VLOOKUPの式をそのまま、Ｃ列に移動し
式のＡ１の部分を
A1*100+B1　とすればよいのでは

=VLOOKUP(A1,・・・・・
を
=VLOOKUP(A1*100+B1,・・・・・
ということです

外したら、次へどうぞ

回答No.5

hallo-2007

No3です。おかしいですね。セルの範囲はあっていると思います。
=MATCH(D2,A:A,FALSE)　で組で指定した最初の行の行番号？
=MATCH(D2,A:A)　で組で指定した最後の行の行番号でますか？
参照の組と番が文字列とかで、検索の組と番が数値とかでは？

別案ですが
=MATCH(D2,A:A)　で組で指定した最後の行番号
=SUMPRODUCT((A2:A21=D2)*(B2:B21>E2))　で番号が指定した以上の数がでますので
=INDEX(C:C,MATCH(D2,A:A)-SUMPRODUCT((A2:A21=D2)*(B2:B21>E2)))
とかでもOでは。

補足

Ａ　Ｂ　 Ｃ　Ｄ　 Ｅ　 Ｆ
1 組　番　名　組　番　名
2　 １ １　 た　■　■　□
3 　２　２　こ
4　 ３　３　ひ
Ｆ２（□）に一度目に回答くださった関数を入れました。
Ｄ２に１，Ｅ２に２を入れると，Ｆ２に「たかし」が出るって事ですよね？？

回答No.4

noname#204879

Sheet1　　　　　　　　　　　Sheet2
　 A　 B　　 C　　　　　　　　　A　B　　C
1　組　番　氏名　　　　　　 1　 1　3　ひろし
2　 1　 1　たかし　　　　　 2　 1　2　こうじ
3　 1　 2　こうじ　　　　　 3　 1　1　たかし
4　 1　 3　ひろし　　　　　 4
5

Sheet2!C1: =INDEX(Sheet1!C$1:C$100,SUMPRODUCT((Sheet1!A$1:A$100=A1)*(Sheet1!B$1:B$100=B1),ROW(A$1:A$100)))

回答No.3

hallo-2007

条件として、参照のデータが組、番の昇順に並んでいる場合です。
仮に　
A　　B　　C　　　D　　　E　　　F
組　番　氏名　　検索組　検索番　氏名
１　１　たかし
１　２　こうじ
１　３　ひろし
とD2、E2に検索したい条件を入れた場合
=VLOOKUP(E2,OFFSET(A1,MATCH(D2,A:A,FALSE),1,MATCH(D2,A:A)-MATCH(D2,A:A,FALSE),2),2,FALSE)
VLOOKUP関数ですが、範囲をOFFSET関数で指定してみました。

補足

早速試したみましたけど，「＃Ｎ/Ａ」が出ました。
数式をクリックすると色枠でセルが表示されますよね？その時に茶色の枠がＡの列に縦にずっと伸びています。あと緑の枠がＡの「組」のところに来てしまっています。訂正の必要がありますか？

回答No.2

marbin

A列に
=B1&"-"&C1
B列に組、C列に番、D列に氏名

A7=1
B7=2
=VLOOKUP(A7&"-"&B7,A1:D3,4)

のほうがいいかな？

回答No.1

marbin

参照用セルには

1-1 A
1-2 B
1-3 C

A7=1
B7=2
=VLOOKUP(A7&"-"&B7,A1:B3,2)
で"B"が返ります。

これではだめですか？

補足

回等ありがとうございます。
10番まで試したら，10は「たかし」が表示されました。もうちょっといじってみます！