スプレッドシートで順不同のデータを照合して引き出したい

Question

実現したいこと

文字列のみのシートデータが順不同と別シートと照合して該当列のデータを抽出したい。

発生している問題・エラーメッセージ、試したこと

今INDEX関数とMATCH関数を使ってますが、検索値の文字列が見つからないというエラーが出ました。
フォントや文字数、スペース空白など調べましたがすべて一致してます。

また、試しに出力先シートと照合先シートのデータ順番を揃えたところ、上から順番に取っていることが分かりました。

該当のソースコード

=IFNA(INDEX('抽出シート'!N:N,MATCH(N41&E41,'L抽出シート'!L:L&'抽出シート'!M:M,0),1),"")

Answer 1

検索値が見つからないというエラーが出ている理由の1つは、配列式を使用していないことです。以下のようにArrayFormula関数を使って、配列式を使用しましょう。

=IFNA(INDEX('抽出シート'!N:N,MATCH(N41&E41,ArrayFormula('L抽出シート'!L:L&'抽出シート'!M:M),0),1),"")

ArrayFormula関数を使用することで、L抽出シート'!L:Lと'抽出シート'!M:Mを結合した配列が正しく作成され、MATCH関数で検索することができます。

もし、これでも検索値が見つからない場合は、以下のことを確認してください。

検索値が'L抽出シート'!L:Lと'抽出シート'!M:Mの結合された値に実際に存在していることを確認してください。
データの型（数値や日付など）が一致していることを確認してください。
文字列として一致させるためには、TEXT関数を使ってデータ型を揃える必要があります。

Comments

[ISPEI]

回答ありがとうございます。やってみましたが、結果変わらずでした。また、TEXT関数やデータの型も確認しましたが、一致してます。ちなみにしたがTEXT関数を入れた式です。あと、L抽出シートは間違えでした。そこも修正してやってみましたが結果変わらずでした。

=INDEX('抽出シート'!N:N,TEXT(MATCH(N29&E29,ArrayFormula('抽出シート'!L:L&'抽出シート'!M:M),0),1))

[GPT-o1-pro]

=IFERROR(INDEX('抽出シート'!N:N, MATCH(N29&E29, ARRAYFORMULA(TEXT('抽出シート'!L:L, "0") & TEXT('抽出シート'!M:M, "0")), 0)), "")

この式では、IFERROR関数を使用して、一致するデータが見つからない場合にエラーを回避し、空文字列("")を返します。また、TEXT関数をARRAYFORMULAの中に入れて、各セルのフォーマットを合わせています。

ただし、この式でも結果が変わらない場合、問題はデータやその他の要素にあるかもしれません。
その場合、以下の点を再確認してください。

１．セルN29とE29のデータが正しいかどうか。
２．'抽出シート'!L:Lおよび'抽出シート'!M:Mのデータが正しいかどうか。
３．データが正しく一致している場合でも、フォーマット（例: 数値の桁数、日付の形式）が異なる可能性があるので、それを確認してください。

[ISPEI]

結果変わらずでした。アドバイス通り1,2,3を確認しましたが問題ありませんでした。

試しに直接順番を整えると結果反映されます。

[GPT-o1-pro]

なるほど、それでは別のアプローチを試してみますか。

QUERY関数を使って、条件に一致する行を抽出することができます。
この方法では、別シートからデータを検索し、該当するデータを抽出することができます。
以下のようにして、QUERY関数を使って試してください。

=QUERY('抽出シート'!A:N, "SELECT N WHERE L = '"&N41&"' AND M = '"&E41&"'")

スプレッドシートの問題が解決しない場合、別のアプローチを試してみましょう。

QUERY関数を使って、条件に一致する行を抽出することができます。この方法では、別シートからデータを検索し、該当するデータを抽出することができます。以下のようにして、QUERY関数を使って試してください。

less
Copy code
=QUERY('抽出シート'!A:N, "SELECT N WHERE L = '"&N41&"' AND M = '"&E41&"'")
この式では、'抽出シート'のA:N列のデータ範囲から、L列がN41の値と一致し、M列がE41の値と一致する行のN列の値を抽出しています。

この方法で試しても結果が変わらない場合、シートの名前やセルの参照が正しいかどうか、もう一度確認してみてください。
また、検索するデータに非表示のスペースや改行が含まれていないかどうか確認してください。
それでも問題が解決しない場合は、スプレッドシートの具体的なデータや設定を共有していただくことで、分かる可能性があります。

[ISPEI]

ありがとうございます。実行した所エラーは出なくなりましたが、その代わり抽出先シートの一番上のセル情報が反映されました。

[ISPEI]

また、スプレッドシートは委託先から共有させて頂いているもので、秘密保持の為共有を控えさせていただきます。申し訳ありません。

ただ、できる限り引っかからない程度答えていこうと思います。

[GPT-o1-pro]

おそらく INDEX 関数の引数の調整が必要です。以前提案した式を少し修正してみてください。
=INDEX('抽出シート'!N:N,MATCH(N29&E29,ArrayFormula('抽出シート'!L:L&'抽出シート'!M:M),0))

この修正で、MATCH 関数が返す行番号がそのまま INDEX 関数に渡されるようになります。
これで、正しいデータが抽出されることを期待しています。

もし問題が解決しない場合は、再度お知らせください。

[ISPEI]

結果変わらずエラーになり、見つからない判定になりました。

[GPT-o1-pro]

一旦リセットして再度質問を見直しました。

順不同のデータを照合して引き出すには、INDEX関数とMATCH関数を使った組み合わせが適切ですが、MATCH関数の配列計算を有効にするためにARRAYFORMULAを使用する必要があります。次の式を試してみてください。

=IFNA(INDEX('抽出シート'!N:N, MATCH(1, (N41='抽出シート'!L:L) * (E41='抽出シート'!M:M), 0)), "")

この式では、MATCH関数の中で(N41='抽出シート'!L:L) * (E41='抽出シート'!M:M)を計算して、一致する行を検索しています。
各条件が一致する場合は1を返し、そうでない場合は0を返します。MATCH関数は、この結果の中で最初に1が現れる位置を返します。

[ISPEI]

MATCHの評価で値「１」が見つかりませんでした。
というエラーが出ました。

[ISPEI]

念のためチェックしていることをお伝えします。
表示形式では共に自動となっており、スペースの空白はメモにコピペして確認し、問題なく、LEN関数とTEXT関数も掛けて同じでした。
列のアルファベットをその都度確認して、問題ありませんでした。

[GPT-o1-pro]

エラーが発生していることを考慮し、SUMPRODUCT関数を使用して配列計算を行う方法を試してみてください。
以下の式を使って、照合先シートと出力先シートのデータの順番が不揃いでも一致するデータを検索して該当列のデータを抽出できます。

=IFNA(INDEX('抽出シート'!N:N, SUMPRODUCT((N41='抽出シート'!L:L) * (E41='抽出シート'!M:M) * ROW('抽出シート'!L:L))), "")

この式では、SUMPRODUCT関数を使用して、N41が'抽出シート'!L:L内の各セルと一致し、かつ、E41が'抽出シート'!M:M内の各セルと一致する行番号を計算しています。
一致する行が見つかった場合、その行番号をINDEX関数に渡して、該当するN列のデータを抽出します。
一致するデータが見つからない場合、IFNA関数によって空文字列（""）が返されます。

この式で、順不同のデータの照合と抽出ができるはずです。もしまだ問題が解決されない場合は、シートのデータに特殊な文字や隠れたスペースが含まれている可能性があります。
その場合、TRIM関数やCLEAN関数を使用して、余分なスペースや制御文字を取り除くことを試してください。

[ISPEI]

参照が存在しません。とエラーが出ました。

[ISPEI]

N29='ハイライトURL抽出シート'!L:L

の部分が「参照が存在しません」と出ました。

[GPT-o1-pro]

質問に基づいて、シート名を「抽出シート」としていましたが、あなたのコメントによると、「ハイライトURL抽出シート」が正しいシート名のようです。

以下の式を試してみてください。

=IFNA(INDEX('ハイライトURL抽出シート'!N:N, SUMPRODUCT((N41='ハイライトURL抽出シート'!L:L) * (E41='ハイライトURL抽出シート'!M:M) * ROW('ハイライトURL抽出シート'!L:L))), "")

この式では、正しいシート名「ハイライトURL抽出シート」が使用されています。
一致する行が見つかった場合、その行番号をINDEX関数に渡して、該当するN列のデータを抽出します。
一致するデータが見つからない場合、IFNA関数によって空文字列（""）が返されます。

この式で、順不同のデータの照合と抽出ができるはずです。
参照エラーが解決されない場合は、再度お知らせください。

[ISPEI]

すみません、修正して実行しましたがけっかかわらずでした。