「伝票Noが1000・1002・1005のどれかに一致する行」をまとめて抽出するには、条件リストをMATCHで照合し、ISNUMBERの判定をFILTERに渡します。候補を別セルに並べるため、数式にOR条件を追加する手間を減らせます。
練習用Excelファイル（無料ダウンロード）
条件リストはA3:A5、元データはA12:E15、結果はA7からです。条件リストの伝票Noを変更して、対象行の変化を確認できます。Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
AND（かつ）・OR（または）の条件を組み合わせて抽出するときは、FILTER関数で複数条件を指定する方法で書き方をまとめています。
FILTER 関数の基本（書き方・いろいろな条件・並べ替え・#CALC! の直し方）は、FILTER関数の使い方にまとめています。
条件をリストに並べる
A3:A5に検索したい伝票Noを入力します。元データはA12:E15で、照合する伝票NoはA12:A15です。結果を置くA7へ次の式を入力します。
ポイントMATCHの0は完全一致です。元表の各伝票Noを候補リストから探します。見つかると位置番号、見つからないと#N/Aになるので、ISNUMBERで抽出用のTRUE/FALSEに変換しています。
候補の数だけORを書かなくてよい
ポイント候補が3つなら「A列=候補1」＋「A列=候補2」＋「A列=候補3」でも書けますが、数が増えると式が長くなります。リスト方式ならA3:A5をA3:A10などへ広げるだけです。
注意条件リストを増やす場合、元表や結果の領域と重ならない場所へ移してください。例題のセル位置のまま無制限に下へ増やすことはできません。
空白の元データを除外する
注意条件リストに空白があると、元表の空白も対象になる可能性があります。IDが入力された行だけ欲しい場合は「空白ではない」という比較を追加します。数値の0が有効なIDなら、0まで除外しないよう注意してください。
対応バージョン確認メモ：Excel 16（Microsoft 365）で試すと、条件リストにA5のような空白セルがあっても、元データの空白行は一致しませんでした（練習ツールの「元データの3行目を空にする」で確認できます）。ただし、バージョンや入力の状態で動きが変わる可能性があるため、「空白ではない」条件を足しておくと、意図が明確になります。
重複と並び順の扱い
ポイント条件リストに1000を2回書いても、元表の同じ行が2回出力されるわけではありません。一方、元表に1000の明細が3行あれば、その3行はすべて返ります。結果は基本的に元表の並び順で、条件リストの順番には並び替わりません。
一致しないときの点検
- IDの数値1000と文字列「1000」が混ざっていないか。
- 前後の空白や全角・半角の違いがないか。
- 照合する列と返す表が同じ行数か。
- 候補に*や?がある場合、MATCHの文字列照合でワイルドカードとして扱われる点に注意する。
ポイント画像は仕組みを示した解説図です。実際の練習ではダウンロードファイルの元データとセル位置を使用してください。
複数列の条件とは分けて考える
ポイント本記事は「1つの列に対する複数候補」のOR検索です。「伝票Noと種類の両方に一致」という複数列のAND検索はFILTERで2条件・3条件を指定する方法をご覧ください。FILTERがないExcel 2019・2016では、補助列や詳細設定フィルターで対応します。
練習用Excelファイル（無料ダウンロード）
条件リストはA3:A5、元データはA12:E15、結果はA7からです。条件リストの伝票Noを変更して、対象行の変化を確認できます。Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
コメント