エクセル 複数列を検索し左3列目を返す

質問

よろしくお願いします。

シート１の５列（E，J，O，T，Y）の中から，該当するセルを検索，該当セルの左隣3列目のセルを返す関数を考えています。検索条件の重複はありません。

検索結果を表示するシートをシート1とは別に作成します。
A列に検索条件を入力し，B列に結果を返したいです。

例シート1
　　A　　B　　　C　　　D　　　E　　　　F　　G　　　H　　　　I　　　　　J　　　・・・・・
　　ID　品名　項目　単価　1年目　ID2　2品名　2項目　3単価　2年目 ・・・・・
１　1　　a　　　b　　　c　　　　1-1　　　e　　　　f　　　　g　　　h　　　1-11
2　2　　i　　　　j　　　k　　　　 2-2　　m　　　　n　　　　o　　　ｐ　　　2-22

　検索結果
　　A　　　B
1　1-1　　a
2　2-22　 n

このような感じのファイルになっています。IFやLOOKUPなどを試しましたが，複数列をうまく検索する関数を作成することができませんでした。
また，検索対象となるシートが複数になる可能性もあり，その際にも対応できるとありがたいです。

よろしくお願いいたします。

回答No.4

keithin · ベストアンサー

＞日付

それならそうと最初から情報提供を。

数値を参照してきたいなら
=IFERROR(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!E:E,0)),0)
+IFERROR(INDEX(Sheet1!G:G,MATCH(A1,Sheet1!J:J,0)),0)
+　…以下同文

セルの書式はもちろん日付等適切に。

補足

回答ありがとうございました。説明不足で申し訳ありません。

上手く作成することができました。ありがとうございました。

回答No.3

KURUMITO

難しい関数を使うことで計算に負担がかかります。
簡単な式で対応するには作業列を作って対応します。
シート1の2行目から下方にデータがあるとしてＺ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTA(E2,J2,O2,Y2)=0,"",E2&"/"&J2&"/"&O2&"/"&T2&"/"&Y2)

結果はシート2に表示させるとしてＡ2セルから下方に検索の文字を入力するとすればＢ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",INDEX(Sheet1!A:Y,MATCH("*"&A2&"*",Sheet1!Z:Z,0),MATCH(A2,INDEX(Sheet1!A:A,MATCH("*"&A2&"*",Sheet1!Z:Z,0)):INDEX(Sheet1!Y:Y,MATCH("*"&A2&"*",Sheet1!Z:Z,0)),0)-3))

シート3にシート1と同じような表があるとしたらシート3のＺ2セルにはシート1と同じ式を入力して下方にドラッグコピーします。

まとめのシート2ではＢ2セルには次の式を入力して下方にドラッグコピーすればよいでしょう。

=IF(A2="","",IFERROR(INDEX(Sheet1!A:Y,MATCH("*"&A2&"*",Sheet1!Z:Z,0),MATCH(A2,INDEX(Sheet1!A:A,MATCH("*"&A2&"*",Sheet1!Z:Z,0)):INDEX(Sheet1!Y:Y,MATCH("*"&A2&"*",Sheet1!Z:Z,0)),0)-3),"")&IFERROR(INDEX(Sheet3!A:Y,MATCH("*"&A2&"*",Sheet3!Z:Z,0),MATCH(A2,INDEX(Sheet3!A:A,MATCH("*"&A2&"*",Sheet3!Z:Z,0)):INDEX(Sheet3!Y:Y,MATCH("*"&A2&"*",Sheet3!Z:Z,0)),0)-3),""))

お礼

回答ありがとうございました。

長文の関数に驚きましたが，よく考えると，納得しました。

回答No.2

maron--5

B1=INDEX(Sheet1!$A:$J,SUMPRODUCT((Sheet1!$A$1:$Z$10=A1)*ROW($A$1:$A$10)),SUMPRODUCT((Sheet1!A1:Z10=A1)*COLUMN($A$1:$Z$1))-3)
★下にコピー

補足

回答ありがとうございます。

早速使ってみました。1行目は上手くいったのですが，コピーした2行目以降が「♯N/A」となりました。
関数中のSheet1!A1:Z10をSheet!$A$1:$Z$10に修正したのですが，よろしいでしょうか？

また，同様の質問ですが，参照先が日付の場合に，おそらくシリアル値と思われる数値になります。
書式設定でも日付表示になりませんでした。どのように対処したら良いのか，ご教授お願いいたします。

回答No.1

keithin

ご利用のエクセルのバージョンが不明ですが、仮にExcel2007以降を使っているなら
=IFERROR(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!E:E,0)),"")
&IFERROR(INDEX(Sheet1!G:G,MATCH(A1,Sheet1!J:J,0)),"")
&　…以下同文

Excel2003以前を使っているなら
=IF(COUNTIF(Sheet1!E:E,A1),INDEX(Sheet1!B:B,MATCH(A1,Sheet1!E:E,0)),"")
=IF(COUNTIF(Sheet1!J:J,A1),INDEX(Sheet1!G:G,MATCH(A1,Sheet1!J:J,0)),"")
&　…以下同文

などのように、順序良く探していけば出来ます。

＃参考
状況によりますが
=INDIRECT("Sheet1!"&TEXT(SUMPRODUCT((Sheet1!$A$1:$Y$10=A1)*ROW(Sheet1!$A$1:$Y$10)*100+(Sheet1!$A$1:$Y$10=A1)*(COLUMN(Sheet1!$A$1:$Y$10)-3)),"!R0!C00"),FALSE)
などのようにしても計算できるかもしれません。

＞複数のシート
最初に回答した数式のバリエーションで出来ます。

補足

回答ありがとうございます。

エクセル2007を使用しています。
1番目の関数を実行したところ，思った通りの結果が返されました。
ただ，参照先のセルが日付の場合，おそらくシリアル値と思われる数字が返ります。
セルの書式を日付にしても変更されませんでした。
どのように対処したらよいでしょうか？
ご教授よろしくお願いいたします。