Excelにてテーブルからの検索について

質問

又又お知恵を拝借です～

以下のテーブルから検索をさせたい場合、どういう関数を使えばいいんでしょう？？
index関数やらmatch関数でやってみてもうまく表現できないのです。。。
宜しくお願いします。

表１）
コード　　数値１　　数値２　　数値３
AB　　　　５０　　　　８０　　１５０
CD　　　　２０　　　　１０　　　３０
EF　　　　３３　　　　６６　　　９８

上記のコード表があり、例えば、あるセルに、２０という値が入ったら、２０と入ったセルのとなりに、CDというコードを表示させたいんです。
８０という値が入ったら、ABというコードを隣に表示という具合に。。。

この表の体型を変えずに何か方法はありますか？

回答No.2

omusupa · ベストアンサー

INDEXとMATCHを使って作ってみました。（少々長いですが、MATCHの範囲が違うだけですので
コピーをうまく使ってください。

=IF(ISNA(INDEX(A2:A4,MATCH(A7,B2:B4,0),1)),"",INDEX(A2:A4,MATCH(A7,B2:B4,0)))&IF(ISNA(INDEX(A2:A4,MATCH(A7,C2:C4,0),1)),"",INDEX(A2:A4,MATCH(A7,C2:C4,0)))&IF(ISNA(INDEX(A2:A4,MATCH(A7,D2:D4,0),1)),"",INDEX(A2:A4,MATCH(A7,D2:D4,0)))

（例題の表をA1番地から作成して、A7に検索値を入れた場合です）

解説をすると・・・・
まず、A2からA4まで検索したい数字があるか、探します（MATCH関数を使用しています）
目的の数字があれば、INDEX関数を使って目的の文字を表示します。
そして、目的の数値がなければ、if関数とISNA関数を使って空白（””）を返します。

これを＆でつなげているという感じです。
つまり空白と文字をくっつけても、問題ないですから・・・

どうでしょうか？

お礼

ご解答有難うございましたぁ～

感激です！！出来ましたぁ～～

テーブルにこだわらず、列単位で検索させていけばいいんですね。
その上、空白と＆で繋げるっと言うアイデアは今までに考えた事もありませんでした。。。

検索範囲に名前をつけて関数をネストさせたら、より判りやすくなりました。

有難うございました！！！

回答No.3

imogasi

お好みで無いかもしれないが、簡潔に出来るという例示。
（１）VBE画面の標準モジュール（シート画面で、ＡＬＴ＋Ｆ１１，ＡＬＴ＋Ｉ，Ｍで出る）に
Function kensaku(a)
Dim cl As Range
For Each cl In Range("b2:d6")
If cl = a Then
kensaku = Cells(cl.Row, 1)
End If
Next
End Function
を貼りつけてください。本番ではRange("b2:d6")を適当に
変えてください。
（２）テストデータ（Ａ１：Ｄ６のＡ２：Ｄ６の部分）
（Ａ）　（Ｂ）　（Ｃ）　　（Ｄ）
32 d
a 20 50 60
b 10 15 23
c 12 34 45
d 1 32 11
e 33 56 87
（３）関数式
Ｂ１に=kensaku(A1)と入れる。
（４）利用
Ａ１の３４と入れる。Ｂ１はｃとなる。
Ａ１の１１と入れる。Ｂ１はｄとなる。

お礼

回答有難うございます～
でも、どーもBVが苦手でよくわかんないんです。。。。（涙）

テストデータ通り、やってみたんですが、name？？っとなってしまいました。

既にある関数やツールでやってみたいです。

有難うございました。。。

回答No.1

maruru01

こんにちは。maruru01です。

MATCH関数（やVLOOKUP関数）は、検索範囲に複数行を指定出来ないようです。
したがって、ちょっと（かなり）工夫がいります。
前提条件として、表中に同じ数値が複数入っていないとします。
表がA1:D4で、数値範囲がB2:D4（A列がコード、1行目が項目名）とします。
仮にF2に数値を入力する場合、隣り（G2）に、

「=OFFSET($A$1,IF(ISERROR(MATCH(F2,$B$2:$B$4,0)),0,MATCH(F2,$B$2:$B$4,0))+IF(ISERROR(MATCH(F2,$C$2:$C$4,0)),0,MATCH(F2,$C$2:$C$4,0))+IF(ISERROR(MATCH(F2,$D$2:$D$4,0)),0,MATCH(F2,$D$2:$D$4,0)),0,1,1)」

これで一応出来ます。
MATCH関数の第2引数の範囲（行数）は適当に変更して下さい。
数値1～数値3なので、上記のように強引に繋げられましたが、数値4、数値5・・・と数が多いと、面倒ですが。

お礼

ご解答有難うございました。

offset関数って初めてです。

今回は、なじみのある関数で回答を下さった方がいましたので、そちらで解決できました。

また、時間のあるとき、offset関数にもTryしてみようと思います。

どうもありがとうございました。