Excel 2列の値を返す数式についてです

大変お世話になっております。
メインシートである 【Sheet１】のセルB2に、このような数式が入っております。
＝IFERROR(INDEX(Sheet2!$B$1:$B$9,MATCH(J2,Sheet2!$A$1:$A$9,FALSE)),"")
上記に加えて【Sheet１】のセルB2に数式を追加したいです。

Sheet4に３列の値があります。
【Sheet4】
　　　A列　　　　B列　　　　 C列
１　　項目　　　コード１　　コード２　　　　　　
２　外国語教育　 ８００　　　 ５０
３　科学教育　　 ９５０　　　 ６０
４　数学教育　　１０００ 　　 ３０　

【Sheet4】のA列（項目）にある値を、メインシートである 【Sheet１】のB列に返したいです。

メインシートである 【Sheet１】のX列にコード１（【Sheet4】のB列）、【Sheet１】のAC列にコード２（【Sheet4】のC列）が入っており、この値を「コード１『且つ』コード２」を条件としたいです。

お時間を頂きまして大変恐縮ですが、ご回答を心よりお待ちしております。
どうぞ宜しくお願い申し上げます。

No.1 ベストアンサー 回答者： goomania 回答日時： 2022/11/26 12:55

前回のご質問者の質問に回答したものです。

前回と似たようなご質問ですが、状況が変わったということでしょうか?

>上記に加えて【Sheet１】のセルB2に数式を追加したいです。
と言われても前回の質問を知らない人には何のことか判らないと思います。

追加したいという以上、優先順位は元の数式が優先するとして、
「現在の数式で空白が返る条件のとき、【Sheet4】のA列（項目）にある値を、【Sheet1】のB列に返すという数式を追加したいです。」
と解釈しました。

これも前回の回答と同じくIFERROR(INDEX～(MATCH(～)),"")という数式を「入れ子」にすればよい話だと思います。
添付画像をご覧ください。前回回答と同様です。
ご質問者が掲示した数式は、
=IFERROR(INDEX(Sheet2!$B$1:$B$9,MATCH(J2,Sheet2!$A$1:$A$9,FALSE)),"")･･･(a)
ですが、画像①は数式(a)のとおり、「sheet1のJ2と同じ値をsheet2にある表のA列から探して、見つかったらその表の同じ行のB列の値を表示し、見つからなかったら「空白」を表示する」という状態を示しておいます。

とりあえず数式(a)に使うsheet2の表は画像③のように「ひらがな(あいうえお･･･)」を「アルファベット(ABCDE･･･)」に置換するという単純なものにしてあります。従ってJ2に「あ」がある場合、B2に「A」が表示されます。

また、
>【Sheet１】のX列にコード1（【Sheet4】のB列）、【Sheet１】のAC列にコード2（【Sheet4】のC列）が入っており、この値を「コード1『且つ』コード2」を条件としたいです。
とのことなので、画像④のようにsheet4に項目とコード1、コード2の一覧表があるものとします(とりあえず9行目までとしています)。

画像②のように、シート1のB2に

=IFERROR(INDEX(Sheet2!$B$1:$B$9,MATCH(J2,Sheet2!$A$1:$A$9,FALSE)),IFERROR(INDEX(Sheet4!$A$2:$A$9,MATCH(Sheet1!$X2&Sheet1!$AC2,INDEX(Sheet4!$B$2:$B$9&Sheet4!$C$2:$C$9,0),FALSE)),""))

という数式を記述します。

この数式のポイントは「INDEX(Sheet4!$B$2:$B$9&Sheet4!$C$2:$C$9,0)」の部分で、MATCH関数の検索範囲を「INDEX関数を使って結合文字の配列にする」というところです。

これで、添付画像②のようにJ2にカタカナ(画像③のA列に存在しないもの)を入力すると、X列とAC列の値に従って、B2にsheet4の項目である「科学教育」が表示されることが判ります。

但し、上記数式が有効となるには前提条件があります。それは、「コード1とコード2を結合したとき、必ずユニークなものになる」というものです。
例えばコード1が「50」コード2が「110」だと結合結果は「50110」になりますが、コード1が「501」コード2が「10」でも結合結果は「50110」となり両方とも同じになってしまいユニークになりません。
このようなケースでは上記数式は正しい結果を返しません。

この回答へのお礼

goomania様…!
大変お世話になっております。
お休みの所、ご連絡とご回答を下さいまして本当に有難うございました…!

今、goomania様の数式を該当セルに入力をさせて頂いた所、すぐに反映することが出来ました…!!
この度も素晴らしい数式をご提示下さいまして、また大変解り易い図も作成して頂きまして、心より感謝申し上げます…!

最初のご回答者様が goomania様でしたので、ご親切な方に出会う事が出来まして、そして２回目にもご回答を下さいまして深く感謝申し上げます…!
いつも本当に有難うございます!!

＞追加したいという以上、優先順位は元の数式が優先するとして、
「現在の数式で空白が返る条件のとき、【Sheet4】のA列（項目）にある値を、【Sheet1】のB列に返すという数式を追加したいです。」
と解釈しました。

有難うございます…! その通りです!!

＞この数式のポイントは、MATCH関数の検索範囲を「INDEX関数を使って結合文字の配列にする」というところです。

『結合文字』を使う事、理解致しました…! お教え下さいまして、本当に有難うございます…!!

＞例えばコード1が「50」コード2が「110」だと結合結果は「50110」になりますが、コード1が「501」コード2が「10」でも結合結果は「50110」となり両方とも同じになってしまいユニークになりません。
このようなケースでは上記数式は正しい結果を返しません。

事前にご注意を下さいまして有難うございます! 組み合わせを再度確認し、そのようなケースには工夫致します…! 数式で手入力のエラーが判明したり、また逆の場合もあるため、注意を致します…。何から何まで本当に有難うございます!!

実は、最初に質問で提示させて頂いた数式と、前回 goomania様がお教え下さいました数式と、そして今回 goomania様がご教授下さいました数式とこの３数式をB列に反映したいのです…。
都合上、列の追加が出来ないため、様々な条件をこのB列に加えています…。
条件の整理をし、どうしても出来ない場合はまた質問をさせて頂きます…。

この度はご親切と大切なお時間を頂戴致しまして本当に有難うございました!
感謝の気持ちで一杯です…!!!
大変恐縮ですが、引き続きどうぞ宜しくお願い申し上げます…!!

お礼日時：2022/11/26 22:04