エクセルで獲得率で評価を判断して解約率でランクをダウンにしたい

自分なりにしらべたのですがどうしても難しくて
ご教授お願いします。
当方をＨＬＯＯＫＵＰやＩＮＤＥＸ+ＭＡＴＣＨ　関数などは使えるつもりくらいのレベルです
マクロなどは使用なしでお願いします。

画像みてもらって質問はＦ３：Ｆ５になります
現在は関数ではなくて手打ちで入れてますがそれを自働入力したいです。
獲得率が２０％、４０％、６０％。８０％、１００％で　ＥＤＣＢＡの評価
解約率が３０％を超えたら　ワンランクダウン　（獲得率がＢ評価の人はＣ評価までおちる）
さらに８０％超えたら　２ランクダウン（獲得率がＢ評価の人はＤ評価）
Ｃ５には　HLOOKUP(B5,I2:M3,2)をいれて獲得率におうじて評価を変更することができましたが
解約によるランクダウンがわかりません。
どなたか私にご教授ください

No.3 回答者： moto_koukousei 回答日時： 2015/10/23 18:04

NO.2の回答を修正します。

Hypnomaticさんの読図を参考に考えた場合、エクセルの表の一部を変えたらどうなのかなと思いました。
参照するテーブルを変更した図を添付します。

No.2 回答者： moto_koukousei 回答日時： 2015/10/23 17:45

もとの画像が読み取れないのですが、Hypnomaticさんの読図を参考に考えた場合、エクセルの表の一部を変えたらどうなのかなと思いました。

参照するテーブルを変更した図を添付します。

結果として使った式は
C列　HLOOKUP(B★,I$2:N$3,2,TRUE)
E列　HLOOKUP(D★,I$5:K$6,2,TRUE)
F列　CHAR( CODE(C★)+HLOOKUP(D★,I$5:L$7,3))

No.1 回答者： Hypnomatic 回答日時： 2015/10/21 00:21

できれば、表を書き直した方が式も単純で、後から修正も楽になるかと思いますが、とりあえず質問されているものに対しての回答になります。

また、添付画像が小さくて正しく読み取れないのと、質問にもいくつか不明点がありますので、以下の前提とします

・A列は人物の名前（評価されるもの）
・B列は獲得率（手動入力）
・C列は評価（関数による自動入力）
・D列は解約率（手動入力）
・E列は解約率によるダウン（関数による自動入力）
・F列は獲得率と解約率を考慮した総合評価（関数による自動入力）

※質問の解約率の表の下には「○ランクダウン」と文字で記載されていますが、関数式を作成するにあたって、より煩雑になってしまうので、今回は「２ランクダウン→-2」、「１ランクダウン→-1」と入力することにします


では本題です。
使う関数はIF、ISERROR、INDEX、MATCH、VLOOKUP関数になります。

１．Index関数
INDEX(範囲,行番号,列番号)

範囲＝I3:M3
（獲得率の表の評価文字：ABCDEの範囲を指定）
行番号＝１
（ABCDEの１行しかないので、行番号は１）
列番号
（後述のMatch関数とHLOOKUP関数を用いて指定します。すなわち獲得率による評価文字をMatch関数から探し、解約率による評価の減少数をHLOOKUP関数にて探します）

２．Match関数
MATCH（検査値,検査範囲,照合の型)

検査値＝C3
（1番目の人物の獲得率に対する評価文字を指定）
検査範囲＝I3:M3
（獲得率の表から、検査値の文字を探します）
照合の型＝０
（検査値が文字なので指定しなくてもいいですが、０で完全一致）

３．HLOOKUP関数
HLOOKUP（検索値,範囲,行番号,検索の型)

検査値＝D3
（手動入力した解約率を指定）
範囲＝I5:J6
（解約率の表を指定）
行番号＝２
（解約率によってさがるランクの表を指定）
検査の型＝１
（検査値（解約率）以下の最大の値を検索）

４．ISERROR関数
ISERROR(テスト対象)

テスト対象がエラーの時に真（True）、エラーではないときに偽（False）を返します

例えば、獲得率でEランクを取り、解約率で１ランクor２ランクダウンを受けた時、Eランク未満はありませんからエラーになりますので、その判定に用います

５．IF関数
IF(論理式,真の場合,偽の場合)

理論式＝ISERROR関数の結果を受け取り真の場合（E評価未満となった場合）、偽の場合（E評価以上の場合）に分けます

真の場合＝”E"
※すなわち、総合評価がE未満だった時、上記の理論式はISERROR関数の結果を受けて、”真”になるので、評価はEを指定します

偽の場合＝理論式に用いたのと同じ数式を入れます
E評価未満とはならない場合の評価を返します。すなわちIndex、Match、Hlookup関数から得た評価文字です




最終的に、１人目の総合評価（F3セル）に入る式は以下になります。
=IF(ISERROR(INDEX($I$3:$M$3,1,MATCH(C3,$I$3:$M$3,0)+HLOOKUP(D3,$I$5:$J$6,2,1)))=TRUE,"E",INDEX($I$3:$M$3,1,MATCH(C3,$I$3:$M$3,0)+HLOOKUP(D3,$I$5:$J$6,2,1)))