Excel 別シートを参照して表の作成

画像参照をお願いします。

sheet2：1つのテーブルには職種（B列）・等級（C列）・基本給（D列）が一覧になっています。
sheet3：もう1つのテーブルには職種ごとの各等級の号俸と基本給が一覧になっています。

sheet2の職種・等級・基本給をもとに、sheet3を参照して新しい号俸・基本給をE列・F列に表示させたいです。
（画像では手作業で赤字で入力しました）

＜ルール＞
①職種・等級の変更はしない。
②基本給は、sheet2と同額とする。同額がない場合は、すぐ上の近似値とする。
③sheet2の基本給がsheet3の表から上回っている場合は、5号俸の基本給とする。（四郎・五郎のパターン）

必要であれば、表の並び方等の組み替えは可能です。
よろしくお願いいたします。

No.1 回答者： ぐーるぐる 回答日時： 2020/12/21 10:44

これ、練習問題？それとも、実際に必要なもの？

どの関数使ってくれとか言う指定があるのかな？

そうでなければ、営業と、事務職の価格は、別物なんだろうから、
別けて、別々に計算すべきですね。
もしくは、等級と新号俸の記号・番号を別けるべき。
どっちかに整理しなきゃ、馬鹿なデータ管理になりますね。

No.2 回答者： mike_g 回答日時： 2020/12/21 13:14

》 画像参照をお願いします

參照に耐えられ無い、畫像が、不鮮明過ぎる！

No.3 ベストアンサー 回答者： fujillin 回答日時： 2020/12/21 16:00

こんにちは

画像がよく見えないのではっきりしませんが、基本的にはMATCH、INDEXで検索すれば良いパターンだと思われます。

「直上近似値」の条件からすると、表を降順にしておいた方がMATCH検索と相性が良いです。
「営業」と「事務」は参照する表が変わるだけなので、IFで判別して別の式にするか、あるいは、似たような式なので表の参照先を入れ替えれば済むでしょう。


考え方の例として、片方のみの例ですが…
（例示のうちの事務部分だけ。シートも一つにしてあります。）

添付図では、J2セルに
=INDEX(A$3:A$7,IFERROR(MATCH(I2,OFFSET(A$3,,MATCH(H2,B$2:D$2,0),5),-1),1))

I2セルに
=INDEX(B$3:D$7,IFERROR(MATCH(I2,OFFSET(A$3,,MATCH(H2,B$2:D$2,0),5),-1),1),MATCH(H2,B$2:D$2,0))

をそれぞれ入力して、J2:I2を下方にフィルコピーしてあります。

No.4 回答者： mike_g 回答日時： 2020/12/21 22:02

Sheet2!E2: =IFERROR(INDEX(INDIRECT($B2),MIN(IFERROR(MATCH(D2,INDEX(INDIRECT($B2),,RIGHT(C2,1)+1),0),MATCH(D2,INDEX(INDIRECT($B2),,RIGHT(C2,1)+1))+1),6),1),1)

Sheet2!F2: =INDEX(INDIRECT($B2),E2+1,RIGHT(C2,1)+1)

但し、Sheet3 に於いて、事前に範圍 A2:D7、F2:I7 に夫々、事務職、営業職 と云ふ名前を附けて居ます。

【備考】檢證が不十分かも。試してみて下さい。
設けていた作業列内の式を無理に纏めちゃった(さうしなきゃ良かった！)ので、"寿限無"式に成ってます。(*^_^*)