XLOOKUPの複数条件を数式なしで設定するには？
商品と支店の両方が一致する単価を取得する例です。2つの列を結合キーに指定し、別支店の単価が混ざらないように照合します。
商品と支店の両方が一致する単価を取得したいときは、それぞれの列を結合キーとしてつなぎます。JOIN SHEETでは同じ2シート間に追加したすべてのキーが一致する組み合わせを結合できます。条件をまとめる補助列や検索用の数式は不要です。
商品だけでは単価を特定できない場合
架空の発注表では、P10の契約単価がソウル支店で1000、釜山支店で1200です。商品だけで結合するとソウルの発注にも釜山の単価が付きます。この例の金額はすべて同じ単位です。
| 商品 | 支店 | 発注数量 | 取得したい単価 |
|---|---|---|---|
| P10 | ソウル | 2 | 1000 |
| P10 | 釜山 | 3 | 1200 |
| P20 | ソウル | 1 | 2500 |
図はこの架空データと操作の流れを説明するもので、画面キャプチャではありません。
商品と支店をそれぞれつなぐ
- 発注シートと単価シートを読み込みます。発注側には商品・支店・数量、単価側には契約商品・契約支店・単価を用意します。
- 「商品」を「契約商品」へドラッグし「一致する行のみ」を選びます。
- 「支店」も「契約支店」へつなぎ、結合キーに追加します。
- 接続線のシンボルを開き、結合キーが2つあることを確認します。同じ2シート間のキーには1つの接続方法が適用されます。
- 「結果のプレビュー」で支店ごとの単価を確認します。
2つの条件が一致した3行が残る
結果は3行です。P10・ソウルには1000、P10・釜山には1200、P20・ソウルには2500が付きます。実際の結果には、上表の列に加えて接続先の契約商品と契約支店も残ります。
「商品か支店のどちらかが一致」でも使えますか？
この設定は両方が一致するAND条件です。どちらか一方でよいOR条件や、範囲内の価格・最も近い日付を探す近似検索ではありません。
単価が見つからない発注も残す場合は「1つ目のシートの全行を保持」を選びます。どの組み合わせが一致するかは同じ2条件で判断し、一致しなかった発注も単価が空欄の行として残します。
また、単価表に同じ商品・支店の組み合わせが複数あると、すべての一致が結果に含まれます。接続線の「接続状態を確認」→「確認する」から「詳細な数値と例」を開き、重複キーを含む行と重複キーの種類数を確認してください。重複する実際の値は元データで調べます。
件数が増えた場合は結合キーの重複で行数が増える理由も確認してください。処理速度はファイル構造や端末環境によって変わり、Excelより常に高速であることを保証するものではありません。
繰り返しの作業を減らす
シートを接続・比較して、必要な結果を確認できます。
この記事はProプランの利用環境を前提としています。
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Multi criteria Xlookup efficiency problem — Reddit r/excel
複数条件の検索という問題を参考に、商品と支店の架空例を作成しました。質問者がJOIN SHEETを利用したという意味ではなく、元の数式やデータも転載していません。
