(excel)複数列検索で中間一致

質問

SUMPRODUCTとINDEXの組み合わせの質問はあるのですが、
自分の行いたいこととはどうも違うため、質問致します。
なお初心者のため、過去記事を正確に理解できていないかと存じます。
重複する投稿が過去にある場合はご指摘いただけると幸いです。

----
（行いたいこと）

[結果出力]シートに、4列値が入っています。
[結果出力]シートの[乗数]列(★のところ)に、
[Master]シートから参照した値を入力したいと考えています。

====
[Master]シート
A列|B列|C列|D列|E列
部署名|人名|部署コード|職級コード|乗数
--------------------------------------
営業|山田|2200|2|15.85
経理|三輪|2800|4|21.85
技術|鈴木|2600|1|18

==

[結果出力]シート
A列|B列|C列|D列|E列
部署名|人名|部署コード|職級コード|乗数
--------------------------------------
ソリューション営業|山田太郎|2200|2|★
技術２課|鈴木華子|2600|1|★
グループ経理1課|吉田美輪|2800|4|★
====

上記のように、部署名、人名は文字列で、部署コード、職級コードは数字です。
部署名及び人名を中間一致で[Master]シートから検索したく、countifも試したのですが、
うまくいきません。
完全一致であれば、★のセルに次の式を入力すると出せることは分かったのですが・・

=INDEX
(Master!E$2:E$1000,SUMPRODUCT
(
(Master!$A$2:A$1000=$A2)
*(Master!$B$2:B$1000=$B2)
*(Master!$C$2:C$1000=$C2)
*(Master!$D$2:D$1000=$D2)
*ROW(Master!$A$2:$D$1000)
)
-1
)

識者の皆様のお知恵を拝借できないでしょうか。
宜しくお願い申し上げます。

回答No.1

end-u · ベストアンサー

とりあえず叩き台として

=SUMPRODUCT(
ISNUMBER(FIND(Master!$A$2:$A$1000,$A2)*FIND(Master!$B$2:$B$1000,$B2))
*(Master!$C$2:$C$1000=$C2)
*(Master!$D$2:$D$1000=$D2)
*Master!$E$2:$E$1000)

とか

=LOOKUP(10^10,
FIND(Master!$A$2:$A$1000,$A2)
*FIND(Master!$B$2:$B$1000,$B2)
*(Master!$C$2:$C$1000=$C2)
*(Master!$D$2:$D$1000=$D2)
*Master!$E$2:$E$1000)

...のような感じになるかと。10^10 は適当です。乗数の最大値以上であれば良いです。

あまりおすすめしたくはないのがホンネです。
使い捨て的な、とりあえず引っ張れればいい、みたいなものであれば良いかもしれませんが。
そのMasterシートではいずれ破綻しそうな気がしますので、見直しをしたほうが良いと思います。
普通に考えれば、個人別コードによるマスタ管理するのではないでしょうか。

回答No.3

end-u

失礼m(_ _)m
『Master!$A$2:A$1000』という記述からして、Masterが追加された場合も考慮し、参照範囲を多めに取ってありますか？
空白セルが参照範囲にある場合は私が提示した式ではうまくいきません。

=LOOKUP(1,1/(FIND(Master!$A$2:$A$1000,$A2)*FIND(Master!$B$2:$B$1000,$B2)*(Master!$C$2:$C$1000=$C2)*(Master!$D$2:$D$1000=$D2)),Master!$E$2:$E$1000)

こんな感じの数式になります。
でも 『1/0 でエラーを作ってLOOKUPで無視させる』というのは他のQ&Aで読み、知識として知ってはいても多分私の発想にはないので別案。

【Masterシートのデータ範囲のみを[名前定義]で設定する方法】
Masterシートの１行目が見出しだとして、[Ctrl]+[F3]キーで[名前定義]。
「名前」を　　　　data
「参照範囲」に　　=OFFSET(Master!$A$1,0,0,COUNTA(Master!$A:$A),5)
あとは
=LOOKUP(0,-FIND(INDEX(data,0,1),$A2)*FIND(INDEX(data,0,2),$B2)*(INDEX(data,0,3)=$C2)*(INDEX(data,0,4)=$D2),INDEX(data,0,5))
としてください。

また、「部署名」と「部署コード」が１対１の関係なら（普通はそう）
A列は見る必要がないので数式から外してください。

でもクドイようですが。
まぁ数式でなんとか処理できるからといってMasterの見直しが進まないとなったら本意ではありません。
Excel活用のテクニック云々前に業務プロセスの改善を優先させるべきかと。

#勉強させて頂きました。m(_ _)m

回答No.2

KURUMITO

それぞれのシートでは2行目から下方にデータがあるとします。
MasterシートはここではSheet1として、また結果出力シートはSheet2として述べます。
シート1のＦ2セルには次の式を入力し下方にオートフィルドラッグします。
=IF(D2="","",C2&"/"&D2&"/"&COUNTIF(F$1:F1,C2&"/"&D2&"*"))
これは部署コードと職級コードが一致する人はかなり少ないものと判断しているからです。
ただ部署コードと職級コードが一人の場合には最後で/0となりますが2人の場合には/1となります。
シート2のＦ2セルには次の式を入力し下方にオートフィルドラッグします。
=IF(A2="","",C2&"/"&D2)
答えのＥ2セルには次の式を入力し下方にオートフィルドラッグします。
=IF(AND(ISNUMBER(FIND(INDEX(Sheet1!A:E,MATCH(F2&"/0",Sheet1!F:F,0),1),A2)),ISNUMBER(FIND(INDEX(Sheet1!A:E,MATCH(F2&"/0",Sheet1!F:F,0),1),A2))),INDEX(Sheet1!A:E,MATCH(F2&"/0",Sheet1!F:F,0),5),IF(AND(ISNUMBER(FIND(INDEX(Sheet1!A:E,MATCH(F2&"/1",Sheet1!F:F,0),1),A2)),ISNUMBER(FIND(INDEX(Sheet1!A:E,MATCH(F2&"/1",Sheet1!F:F,0),1),A2))),INDEX(Sheet1!A:E,MATCH(F2&"/1",Sheet1!F:F,0),5),""))
この式は部署コードと職級コードが同じ人が2人までの式です。もっと多くの人が考えられる場合には/2から/3と変えた式を追加することが必要です。
かなり式は複雑になりますが頑張ってください。
このような面倒な式にしないで解決する方法はそれぞれの人物についてＩＤコードを設定して対応することがより現実的でしょう。