異なるシートから、文字列を検索して数字をつける

質問

表Ａの連番（385,386,387）を表Ｂの文字列（ms151,ms987,ms258）の一致した文字列に当てはめたいです

　（表　Ａ）　　　　　　　　（表　Ｂ）

385 　ms151　　　 　 Ｘ 　Ｙ　 Ｚ
386 　ms258 → ms151 ms987 ms258
387 　ms987

結果として
385　　　 387　　386
ms151 ms987 ms258

としたいです　
また、表Ａ、表Ｂは、異なるシートで出来ています
うまくいかず困っています
　よろしくお願いします

回答No.3

hallo-2007 · ベストアンサー

>ココのＡ２とは表ＢのＸを指すのでしょうか？
その通りです、Xのセルの番地を入れてください。
=INDEX(表Ａ!$A:$A,MATCH(A2,表Ａ!$B:$B,FALSE))
最後に)がひとつ漏れていましたので訂正します。
=MATCH(A2,表Ａ!$B:$B,FALSE) だけですと
B列を検索範囲でA2と同じ値がある行の番号がでます。
INDEX関数でA列指定して、検索した値がある行の値を表示するという意味です。

お礼

大変解かり易く、ありがとうございます
こういう組み合わせとか、なかなか思いつかなくて・・・。
ありがとうございました。

回答No.6

QoooL

#5です。お返事遅くなってごめんなさい。

>４行目
>Range("P5:R5").ClearContents
>コレをやると全部消えるのですが・・・。
>消さないと何か不具合あるのでしょうか？

説明が足りませんでした、すみません。なんの意味もない行です。指定した範囲の内容（値）をクリアにさせています。途中でエラーが起きて止まった時などのために、「これから書き込むぞ」というエリア（ＸからＺまで）をあえて最初黒板消しで消したのですが、邪魔だったら削除してください。

私も、イメージファイルとかが貼れるサイトについては知識不足です。
Microsoftの答えてねっと、も一応Office関係で調べるときにはのぞきますが、やっぱりここの方が回答量が多いし応答も早い気がします。ファイルアップロードのサイト（cocoaなど）に登録してたら、そこのリンクを貼って、自分のファイルを外部にのぞかせることはできるでしょうね。
解決して良かったです。#1さんの方が単純明快な気もしました。

参考URL：

http://www.kotaete-net.net/

お礼

そう、回答量で言うと多いんですよね
ＶＢＡは、根気良く続けて生きたいなと思います
今回は、ありがとうございました。

回答No.5

QoooL

#2、#4です。コマンドボタン1はSheet2にあるとしました。

Sub ボタン1_Click()

KigouRetsu = "E"
BangouRetsu = "D"

Range("P5:R5").ClearContents

For i = 16 To 18 'Pは16番目、Rは18番目

Kensaku = Cells(6, i).Value

Sheets("Sheet1").Select

With Range(KigouRetsu + "2:" + KigouRetsu + "4") '2～4行目の中から検索
Set Location = .Find(What:=Kensaku, LookIn:=xlValues, LookAt:=xlWhole)

If Location Is Nothing Then
Gyo = 0
Else
Gyo = Mid(Location.Address, 4)
End If

End With

If Gyo = 0 _
Then
Kekka = "該当無"
Else
Kekka = Range(BangouRetsu + Gyo).Value
End If

'検索結果貼り付け
Sheets("Sheet2").Select
Cells(5, i).Value = Kekka

Next i

End Sub

ためしてみてください。私はＶＢＡ独学のため、かなりの自己流で細かい点には気が行き届いていないと思います（よって自信も半減）。RangeとCellsがごちゃまぜで見にくいのもご勘弁ください。
検索範囲は、
Range(KigouRetsu + ":" + KigouRetsu)
で列全体を探させても良いと思います。
標準モジュールなどはわかっておられるという前提で書いていますが、もしわからなかったら、私がまた返事するのが遅くなってしまったらごめんなさい。（投稿の際、タブやスペースが削除されるから不便ですね）

お礼

４行目
Range("P5:R5").ClearContents
コレをやると全部消えるのですが・・・。
消さないと何か不具合あるのでしょうか？
とりあえず、なしにしたら出来ました。

タブ　スペースはやりづらいですね
質問するのに文章では、はっきり言い表せなかったので
こうするしかなかったです。
jpegとか、イメージファイルとか貼れれば楽だと思うんですけど。
こういう質問がしやすいサイトとかどこかにあれば、
独学というかやりやすいかなと思いました。

回答No.4

QoooL

#2 です。お返事が遅くなってすみません。

前に私が書いたのと同じ表を使うとします。VLOOKUP の「範囲」の指定をわかりやすくするために、D列をわざと省いて書きます。

　(Sheet1)　　
　　　基準列　　　　　　　　　４列目
行　　　Ｅ　　　　Ｆ　　Ｇ　　　Ｈ　　　　　列
2　　　ms151　　x　　　x　　　=D2 (385)　
3　　　ms258　　x　　　x　　　=D3 (386)
4　　　ms987　　x　　　x　　　=D4 (387)

　(Sheet2)
行　　　Ｐ　　　　Ｑ　　　　Ｒ　　　列
5　　　※1　　　※2　　　※3　
6　　　ms151　ms987　ms258

※1　　=VLOOKUP(P6,Sheet1!$E2:$H4,4,FALSE)
※2　　=VLOOKUP(Q6,Sheet1!$E2:$H4,4,FALSE)
※3　　=VLOOKUP(R6,Sheet1!$E2:$H4,4,FALSE)

まず※１から解説します。
すみません、前回の回答で P6 を P5 と書いてしまいました。循環参照でしたね、訂正します。
VLOOKUP とは、縦（垂直、VERTICAL）方向に、参照する、という関数です。仲間に HLOOKUP があります。
パラメータの４つ目のFALSEはこのまま覚えてください。TRUEもありますがややこしいです。Ｅ列にms258が２回出てくるときなどに処理が異なります。

次に、パラメータの２つ目のSheet1!$E2:$H4が、
ms151を探させる範囲です。シート１のＥ２からＨ４まで、ということですね。このとき、左端は必ず、ms151が含まれているＥ列、とするのがポイントです。

パラメータの１つ目のP6は、Ｅ列から探させる値が入ったセルです。Ｐ６のセルの中身、つまりms151を探すことになります。
※2では Q6 (ms258)
※3では R6 (ms987)
としている意味をわかっていただけますでしょうか。

パラメータの３つ目の４は、
ms151が見つかったときに、同じ行の何列目の値を返すか
です。Ｈ列はＥ列から数えて４番目だから４にしました。Ｆ列なら２です。

まとめると※1については、範囲Ｅ２：Ｈ４の一番左の列の中から、Ｐ６（検索値）と同じ値を持つセルを探し、それと同じ行の、Ｅから数えて４番目の値を返す、という結果です。

Sheet1!$E2:$H4
という書き方の＄は絶対参照というもので、
Sheet1!E2:H4
でも範囲は一緒なのですが、式を横にドラッグした時に範囲がずれないよう、＄を入れました。

関数入力ボックスで入力する時は右側の３色のマークを押すと、直接範囲を選べます。

字数制限にひっかりそうなので、マクロについては別回答とします。

お礼

VLOOKUPは、わかりづらくて避けていました。

お礼が遅くなりすみません。
ご丁寧にありがとうございました。

回答No.2

QoooL

もし表Ａの右と左が逆だったら、関数 VLOOKUP を使うのが一番シンプルだと思います。そこで、

　（表　Ａ）　　　　(Sheet1)　　
行 Ｄ Ｅ Ｈ　列
2 385 　ms151　 =D2　
3 386 　ms258 =D3
4 387 　ms987 =D4

という風に、「一致した文字列」を検索したい文字列よりも右におきます。
（表　Ｂ）のＸのセル（Ｐ5としましょうか）に、
=VLOOKUP(P5,Sheet1!$E2:$H4,4,FALSE)

関数は「検索/行列」のグループの一番下にあると思います。
直接入力するのでなく、関数入力ボックスで入力するなら、
検索値　　Ｐ6
範囲　　　Ｅ2：Ｈ4
列番号　　4　　（ＨはＥから数えて４番目だから）
検索方法　FALSE
と入力します。
右へドラッグすれば、Ｙ、Ｚにも検索結果が入るはずです。

Ｆ２キーを押すと、どこを参照しているかわかっていただけると思います。（Ｄ列は関係ない。Ｅ列から検索範囲となっている）

それとも、マクロを使ってもいいなら、FINDを使う方法もありますが？

補足

ご回答いただきありがとうございます
行 　Ｄ 　　　Ｅ　　　 Ｈ　　列
2　 385 　ms151　 =D2　
3 　386 　ms258 　=D3
4 　387 　ms987 　=D4
すみません　検索値Ｐ５、Ｐ６の辺りがちょっと理解しづらいです

小出しになって申しわけありません
不特定多数が使用するので、コマンドボタンで実行させたいです
FINDを使ったものをぜひお教えていただきたく思います
よろしくお願いいたします

回答No.1

hallo-2007

シート名　表Ａ
　Ａ　Ｂ
385 　ms151
386 　ms258
387 　ms987　として
シート名　表Ｂ
　Ａ　　Ｂ　　Ｃ
　Ｘ 　　Ｙ　 Ｚ
ms151 ms987 ms258　として
Ｘには　=INDEX(表Ａ!$A:$A,MATCH(A2,表Ａ!$B:$B,FALSE)
と入れて右へコピィしてみてください。

補足

ご回答いただきありがとうございます
=INDEX(表Ａ!$A:$A,MATCH(A2,表Ａ!$B:$B,FALSE)
　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　ココのＡ２とは表ＢのＸを指すのでしょうか？