エクセル 関数 文字が一致した場合、別シートのセルに反す

sheet1 　　sheet2
A　　B 　　A　　B
犬 10 　　30　　烏
猫 20
烏 30
牛 40

sheet1の表を元に、sheet2のA列のセルに入力した文字がsheet1のB列の中に一致した場合、左隣のセルの文字をsheet2のB列のセル(右隣)に反す式を作りたいのですが、上手くいかず困ってます。一致した文字が無い場合、空白になるようにもしたいのですが…

どうかご教示宜しくお願い致します！

No.1 ベストアンサー 回答者： yuji3690 回答日時： 2017/03/19 14:51

一致するセルが返すセルより左にある場合はVLOOKUPを使うことで可能です。

データを入れる順番を変えるとか、C列にA列と同じデータを表示するとかすれば、
=VLOOKUP(「検索するデータの入ったセル」,「検索する範囲の上端のセル（もしくはそれより上のセル）：表示するデータの下端のセル（もしくはそれより右下のセル）」,「検索する列を1列目と数えて、表示するデータが何列目にあたるか」,FALSE）
とすることで、目的が達せられます。「」の部分については該当するセル、範囲、数値を指定してください。
範囲については列全体を指定することでも可能です。
最後のFALSEについては、完全一致という意味があります。
具体的には、sheet1のC列にA列のデータを表示させたとして、
sheet2のB1=VLOOKUP(A1,sheet1!B:C,2,FALSE)
となります。
一致するものがない場合に空白を表示させるのであれば、
sheet2のB1=IF(COUNTIF(sheet1!B:B,A1)=0,"",VLOOKUP(A1,sheet1!B:C,2,FALSE))
とすることで、A1と一致するデータがsheet1のB列に0個であった場合に空白、そうでなければ先ほどの式の値、が表示されます。

何らかの事情でこの方法が使えない場合、
MATCH関数を使うことで、一致するデータのある行を計算することができます。
一致する行が分かれば、そのA列なので、INDIRECT関数によってシート、列、行、を指定し、該当するデータを抽出することができます。
具体的には
sheet2のB1=INDIRECT("sheet1!A"&MATCH(A1,sheet1!B:B,FALSE))
という感じですね。
一致するものがない場合に空白を表示させるのであれば、先ほど同様に
sheet2のB1=IF(COUNTIF(sheet1!B:B,A1)=0,"",INDIRECT("sheet1!A"&MATCH(A1,sheet1!B:B,FALSE)))
とすることで、可能ですね。

この回答へのお礼

問題解決できました！ご丁寧に本当にありがとうございました！

お礼日時：2017/03/19 15:43

No.2 回答者： 銀鱗 回答日時： 2017/03/19 15:36

MATCH関数とINDEX関数の組み合わせがスタンダードな方法かな。

Sheet2のA列の値をSheet1のB列の先頭から何番目にあるかを調べ、
Sheet1のA列の先頭から数えて何番目の値を返す…。
とします。

まずMATCH関数は
　＝MATCH(検索値,範囲,照合の型)
のようにして使います。
「照合の型」は完全に一致する値を見つけるために「0」を指定します。
（1を指定すると、一致する値が無い場合は検索値よりも大きく一番近い値を見つけに行き、
　-1を指定しると、一致する値が無い場合は検索値よりも小さく一番近い値を見つけに行きます）

　＝MATCH(A1,Sheet2!B:B,0)

こう指定することで、A1セルの値と一致する値が入力されているセルが何番目にあるのかが返ってきます。
続いてINDEX関数は
　＝INDEX(範囲,行位置,列位置)
のようにして使います。
範囲が1行だけ、または1列だけの場合はそれぞれ列位置、行位置を省略できます。

　＝INDEX(Sheet1!A:A,MATCH(A1,Sheet2!B:B,0),1)　…列位置は省略可能。

これで目的を達成できます。

ただし、MATCH関数で一致する値が見つけられなかった場合エラーになります。
エラーの処理は自身で考えてください。

この回答へのお礼

いろいろと勉強になりました！ご丁寧に本当にありがとうございました！

お礼日時：2017/03/19 15:47