[[20160331202651]] 『VLOOKUPとIFの使い方』(ありさ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『VLOOKUPとIFの使い方』(ありさ)

VLOOKUPで式を作り方を教えてください。

sheet2で範囲指定してsheet1に結果を表示させたいです。
sheet2の状態は
A列は10桁のコード。全ての行が左から3桁は同じ数字です。
B列には氏名が入っています。
C列は半角カタカナのフリガナ
(カタカナの前に0か1から始まる3桁の数字が入っている場合もあります)

sheet1のA列にsheet2のA列の10桁のコードの内、
4桁〜10桁目の数字を入力すると、sheet1のB列に該当する氏名が表示されるようにしたいです。
また、sheet1のC列は該当するフリガナが表示されるようにしたいのですが、
フリガナのみの場合は空白、フリガナの前に0か1から始まる3桁の数字がある場合、
その3桁の数字のみを表示させたいです。

例えを出しますと
sheet2のA1のセルに1231111111
sheet2のB1のセルに山田太郎
sheet2のC1のセルに022アイウエオ
と入っているとします。

sheet1のA列に1111111と入力すると
B列には山田太郎、C列には022と表示させたいです。
氏名の方については、A列に1231111111と入力するとB列に表示されますが
始めの123は入力しないようにしたいです。
検索値に”*”&A1や123&A1と入力しましたができません。
sheet2のコードの左3桁を消すこともできません。

フリガナの前の数字についてはifやleftなどを使っていますが、できません。

何か方法はありませんでしょうか。

< 使用 Excel:unknown、使用 OS:Windows7 >


 とりあえず、B1 は =IFERROR(INDEX(Sheet2!B:B,MATCH(A1,VALUE(MID(Sheet2!$A$1:$A$100,4,7)),0)),"")

 これを、Ctrl/Shift/Enter でいれると、取得できそうですね。
 (もっとスッキリした式があるんだと思いますが)

(β) 2016/03/31(木) 22:15


 >検索値に ・・・123&A1と入力しましたができません。

 123を加えたことにより、文字データ化したのに、
 Sheet2の方が数値データなので、マッチしないという現象だと思います。

           数値に戻してやる
            ↓
 (1) B1セル =VLOOKUP(VALUE(123&$A1),Sheet2!$A:$B,2,FALSE)

 (2) C1セル =MID(LOOKUP(2000,1*(LEFT(1&VLOOKUP(VALUE(123&$A1),Sheet2!$A:$C,3,FALSE),{1,2,3,4}))),2,3)

 <Sheet1 結果図>
  行  ___A___  ____B____  _C_
   1  1111111  山田太郎   022
   2  4567890  岡崎真司   124
   3  9876540  ハリル        

 <Sheet2>
  行  _____A_____  ____B____  ____C____
   1  1231111111   山田太郎   022アイウエオ 
   2  1234567890   岡崎真司   124カキク   
   3  1239876540   ハリル     ハリル   

 ※該当がない時の処理は考慮しておりません。 m(__)m

(半平太) 2016/03/31(木) 22:22


 すでに半平太さんから回答があったので用済みですけど、一応、努力(?)の結果を。

 C1 は B1の方式に、ネットで検索した、文字列から数字を抜き出す式をあてはめて以下にしたら一応でるようです。

 =IFERROR(TEXT(LOOKUP(10^17,LEFT(INDEX(Sheet2!C:C,MATCH(A1,VALUE(MID(Sheet2!$A$1:$A$100,4,7)),0)),COLUMN(1:1))*1),"000"),"")

 これも Ctrl/Shift/Enter で入力。

(β) 2016/03/31(木) 22:26


 あっ! Sheet2 の A列の値の先頭は 123 で固定でしたか。
 なら、式の中に、VLOOKUP を組みこむことができたようですね。

(β) 2016/03/31(木) 22:29


 Excelのバージョンは何ですか?

 2007以降だとして。

 Sheet1のC列が、文字列ではなく数値でもかまわないのなら

 B1 =IFERROR(VLOOKUP((123&A1)*1,Sheet2!A:B,2,FALSE),"")

 C1 =IFERROR(LEFT(VLOOKUP((123&A1)*1,Sheet2!A:C,3,FALSE),3)*1,"")

 C1の表示形式〜ユーザー定義を 000 にする、

 とか。
(笑) 2016/03/31(木) 23:15

 Sheet1のC列、数値ではなく文字列の方がいいのなら

 バージョンは2007以降だとして。

 B1は前回と同じ

 C1 =IFERROR(TEXT(LEFT(VLOOKUP((123&A1)*1,Sheet2!A:C,3,FALSE),3),"000;;;"),"")
 または
 C1 =IF(B1="","",TEXT(LEFT(VLOOKUP((123&A1)*1,Sheet2!A:C,3,FALSE),3),"000;;;"))

 表示形式「標準」

 とか。
(笑) 2016/04/01(金) 22:01

みなさん、ありがとうございました。
無事に完成することができました。
(ありさ) 2016/04/02(土) 09:10

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.