EXCELで検索した値の下のセルの値を表示したい

解決済

質問者：kyuroto
質問日時：2005/06/15 02:42
回答数：4件

たとえば列で

1郵便番号
2123-4567
3電話番号
401-1234-5678
5趣味
6かくかく
7特技
8しかじか

と表示されているとします。
ここから郵便番号を検索し、郵便番号の下のセルの値を表示させるにはどうすればいいでしょうか？

また、範囲指定は同じでも、電話番号であればその下の値が表示される方法です。

VLOOKUPやHLOOKUPではかならず検索される場所が範囲の上端や左端なので悩んでいます。

よろしくお願いします。

通報する

この質問への回答は締め切られました。

質問の本文を隠す

このQ&Aに関連する最新のQ&A

「検索番号」に関するQ&A：スマホで個人の電話番号検索が出来る？

さらに表示

「Excel 検索」に関するQ&A： Excel VBA 検索した値を入力フォームに表示

「検索場所」に関するQ&A：エクセルで、検索語移動し入力後同じ場所に戻る方法を教えてください。

「郵便検索」に関するQ&A：イギリスの郵便番号検索の仕方

「表郵便番号」に関するQ&A：郵便番号と都道府県の対応表がほしい

回答 (4件)

ベストアンサー優先
最新から表示
回答順に表示

No.2ベストアンサー

回答者： grumpy_the_dwarf
回答日時：2005/06/15 08:40

lookup系の関数では、検索して見つかった値が返ってきますが、見つ

けた場所を返す関数もあります。また、範囲内の特定の場所の値を返
す関数もあります。

=match(検索する値, 探す範囲, 探し方)
=index(範囲, x行目, y列目, 領域番号)

これで例えば
=match("郵便番号", A1:A8, 0)
とやると　1　が返ってきますので、もう1行下ってことで+1して
index関数に代入する
=index(A1:A8, match("郵便番号", A1:A8, 0)+1, 1, 1)
と、123-4567が返ってくるわけです。

表の端っこ以外のところで検索しなくちゃ行けない場合に、よく使う
方法ですので、覚えといて損はないですよ。

- 20
- 件

通報する

この回答へのお礼

解決しました！！
ありがとうございました。

通報する

お礼日時：2005/06/17 02:49

No.4

回答者： Poer
回答日時：2005/06/15 20:30

　＃１です。

私は次のような方法を試してみました。

　A7を左上端として、ご質問のような表が入っているとします（仮に表の下端をエクセルの14行目とします）。

(1)関数を入力します。以下、「セル番地・・・　数式」。

　A1・・・　=IF(A2="","条件入力",IF(ISERROR(A4),"不明",A4))
　B1・・・　=IF(B2="","",IF(ISERROR(B4),"不明",B4))
　B2・・・　=IF(AND(A2<>"",ISERROR(C4)),"該当なし",IF(ISERROR(C4),"",C4))
　C2・・・　=IF(COUNTIF(5:5,">0")>=2,"※複数の該当データ","")
　A4・・・　="条件： "&HLOOKUP(1,6:14,MAX(5:5),0)
　B4・・・　="結果： "&HLOOKUP(1,6:14,MAX(5:5)+2,0)
　C4・・・　=HLOOKUP(1,6:14,MAX(5:5)+3,0)
　A5・・・　=IF(ISERROR(MATCH($A2,A7:A14,0)),0,MATCH($A2,A7:A14,0))
　A6・・・　=COUNTIF($A5:A5,">0")

(2)A5とA6を表の右端の列まで（オートフィルで）コピーします。

(3)A2に条件を入力し、B2に結果が出るかを確かめます。

(4)うまくいったら、4行目から6行目までの行幅をゼロにして行を隠します。

（補足）
　A2に、ある項目の結果を入力すると、B2にその一つ下の項目の結果が出ます。
　表の行を増やした場合、関数の中の14という数字を表の下端の行数に変更します。
　該当データが複数の場合、最も左にあるデータが優先的に表示されます。

　お時間があれば試してみてください。

- 5
- 件

通報する

この回答へのお礼

試してみようと思ったのですが、
ややこしくて理解できませんでした。
今度時間があるときに使ってみます。
ありがとうございました

通報する

お礼日時：2005/06/17 02:53

No.3

回答者： mshr1962
回答日時：2005/06/15 09:01

範囲をずらせばいいのでは？

検索するのが郵便番号なら2行目ですよね
=HLOOKUP(検索値,$2:$8,3,FALSE)
これで2行目を検索して、対応する列の4行目(電話番号)を表示になります。

別の方法
=LOOKUP(検索値,$2:$2,$4:$4)
検索値を2行目から検索して、対応する4行目を表示
ただし、2行目を昇順にする必要があります。

=INDEX($2:$8,2,MATCH(検索値,$2:$2,0)-1)
MATCH関数で列位置を検索してINDEX関数で対応するデータを表示

- 4
- 件

通報する

この回答へのお礼

上記の二つは試していたのですがだめでした。
３つ目はためさせていただきました。解決しました。
ありがとうございました。

通報する

お礼日時：2005/06/17 02:51

No.1

回答者： Poer
回答日時：2005/06/15 07:32

　郵便番号の下のセルの値というのは、電話番号の値ということでしょうか？もしそうであるなら、実は先程私なりに作ってみて、うまくいきました。

しかし残念ながら出勤時間が迫っており、夜までご説明する時間がありません。今日の夜にでも間に合えばお答えできるんですけどね（＞◇＜）。

- 2
- 件

通報する

このQ&Aに関連する人気のQ&A

「検索番号」に関するQ&A：ネットで個人の電話番号を調べることはできますか？

さらに表示

「Excel 検索」に関するQ&A： excelで検索値の入っているセル番地を取得するには?

「表郵便番号」に関するQ&A：ワードの差込印刷で、郵便番号を封筒の枠内にキレイに印刷する方法は？？

「検索場所」に関するQ&A：経度・緯度から場所を検索

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう！

質問する（無料）

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか？

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

　=OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))　⇒　A1:F200で値がX1と一致するセルの行番号

＞その「ある範囲」の中には検索したい値が入っているセルは１つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

他の回答も見る

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
（「田中」のセルがA1です。）

　[ 田中 ][ 10 ][ 200 ]
　[ 山田 ][ 21 ][ 150 ]
　[ 佐藤 ][ 76 ][ 250 ]
　[ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

　[ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

　[ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは！
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー！
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

他の回答も見る

QＶＬＯＯＫアップ関数の結果の一つ下のセル表示

他の方の質問をみましたが、いまいちわからないので御回答お願いします。

シート１：Ａ列にに文字列（会社名）が入力してあります。
Ａ列の文字列を参照し、シート２の結果を表示したいです。
シート２はＡ列にシート１と同じ文字列（会社名）、Ｂ列にはその会社に対する、１行目（預金種類）
２行目（口座番号）…となっています。

Ａ列の会社名を参照し、預金種類はＶＬＯＯＫ関数でできましたが、その１行下にある口座番号はどのような関数を使用すれば表示できますか？

宜しくお願いします。

Aベストアンサー

INDEX関数とMATCH関数を組み合わせる方法です。
仮にSheet2にある会社名の行情報をSheet1の列方向に抽出と想定、
Sheet1、Sheet2ともに見出し行ありとしています。

Sheet1のB2に=IF(COUNTIF(Sheet2!$A:$A,$A2),INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)+COLUMN(A1)-1),"")として縦横に必要分コピー

他の回答も見る

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列>　<B列>　<C列>
7/1　りんご　100円
7/2　ぶどう　200円
7/2　すいか　300円
7/3　みかん　100円

このような表があって、100円を含む行をそのままの形で、
別のセル（同じシート内）に抜き出したいのですが。

7/1　りんご　100円
7/3　みかん　100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は５００行までとしていますが、必要に応じて変更して下さい

他の回答も見る

QエクセルのＩＦ関数で、文字が入力されていたならば～

エクセルのＩＦ関数で文字が入力されていたならば～、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またＩＦ関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか？

他の回答も見る

Qエクセル2000で常に1つ上のセルを参照する。

出来るのでしたら教えてください。
質問はタイトルの通りです。
最初はA18のセルに=A17などしていましたが、
行の削除、コピーなどを行なうことがあり、
ちょっと困ってます。
どうか宜しくお願いします

Aベストアンサー

A18に入力する式を、=A17 の代わりに

　=OFFSET(A18,-1,0)

とすれば、セルA17を削除してもエラーは出ません。

こういう意味の質問だったでしょうか。

他の回答も見る

Qエクセル　同じ値を探して隣の数値をコピーする

エクセルで以下の作業を簡単にする方法を教えて下さい。

Sheet1の１行目には見出しがあり、A列とB列の２行目から下にデータが入っています。（約２００行）
A列　　　　　B列
田中　　　　13
山本　　　　 8
谷口　　　　11
鈴木　　　　6
佐々木　　　　9
奥村　　　　 15
・
・
・

Sheet2のA列１行目から下には別のデータが入っています。（約６００行）
A列
太田川
山村
田中
多賀先
鈴木
奥村
幸田
・
・
・

Sheet2のA列のデータと完全一致するデータ（名前）をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける。
見つからない場合はSheet2のB列は空欄のままです。
Sheet2
A列　　　　　　B列
太田川　　　　　
山村
田中　　　　13
多賀先
鈴木　　　　 6
奥村　　　　15
幸田
・
・
・

Aベストアンサー

シート2のＢ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Sheet1!A:A,A2)=0,"",VLOOKUP(A2,Sheet1!A:B,2,FALSE))

他の回答も見る

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか？？
セルの内容ではなくて、番号です。（A1、B3など）
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの３つ目の引数を変えると
$C$4
$C4
C$4
C4
の４つの形に出来ます。

他の回答も見る

QExcel 表から値をさがして隣のセルの値を返す

お願いします、教えて下さい。
VLOOKUPのようにいまから左端に番号などを入れる手間無く、値を返したいです。

一つの目のbook　　　　　２つ目のbook
　Ａ列　　B列　　　　　　Ａ列　　　B列
顧客　　　担当　　　木下麻衣
小野博美　佐藤　　　　　　　　　　↑
田中香織　吉岡　　　　　　　ここに関数を入れて、B列の金田を返す
木下麻衣　金田　　　

同シート上で表をふたつ作っても構いません、どなたか教えて下さい。　

Aベストアンサー

質問者さんの意図にあっているかどうかわかりませんが。

内容を見る限りでは2つ目のbookのA列が検索値に
なるようでしたら、

=VLOOKUP(A1,Sheet1!A1:B5,2)

では如何でしょうか？
1つ目のbookをsheet1、2つ目のbookをsheet2で考えてます。
また、左上よりA1としてますので確認ください。

他の回答も見る

Qエクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてくだ

エクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてください。

　　　　　　　　　　　　　　　Ａ列　　　Ｂ列　　　　Ｃ列
Ａファイルには　行　【注文月日】、【氏名】、【商品金額】が記入されています。
Ｂファイルには　行　【注文月日】、【氏名】、【税金額】が記入されています。

ＡファイルのＤ列に、Ａファイルの【注文月日】かつ【氏名】がＢファイルの【注文月日】かつ【氏名】と一致する場合のＢファイルＣ列の【税金額】を表示したいのです。

ＶＬＯＯＫＵＰ関数では条件が１つだけなので使用できませんが、似たような関数で複数条件ができる関数があるなら教えてください。

Aベストアンサー

こんばんは！
一例です。
↓の画像で右側（Bファイル）の方に作業用の列を設けています。
作業列D2セルを
=A2&B2
としてオートフィルで下へずぃ～～～！っとコピーします。

そして、AファイルのD2セルに
=IF(OR(A2="",COUNTIF([Bファイル.xls]Sheet1!D$2:D$1000,A2&B2)=0),"",INDEX([Bファイル.xls]Sheet1!C$2:C$1000,MATCH(A2&B2,[Bファイル.xls]Sheet1!D$2:D$1000,0)))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
尚、数式はBファイルの1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

他の回答も見る

ページトップ