スキャンした崩れた SKU で検索する（TRIM + UPPER）
問題
問題文
バーコードリーダーは前後に空白を付け、小文字で書き出すため、E1 は sku-103 になっています。一方 A2:C6 の価格表は整った大文字のキーです。そのスキャン値の価格を Price セル E2 に入れ、表にないときは "Not found" を返してください。
スキャン値を式の中で整えます。E1 を書き換えたり、作業用の列を足したりしないでください。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Scanned SKU | sku-103 |
| 2 | SKU-101 | Keyboard | 49 | Price | |
| 3 | SKU-102 | Mouse | 19.5 | ||
| 4 | SKU-103 | Monitor | 24.99 | ||
| 5 | SKU-104 | Dock | 89 | ||
| 6 | SKU-105 | Cable | 12.5 |
ポイント解説
TRIM が前後の空白を除き、UPPER が大文字に揃えるので、整えたキーが表と一致します。XLOOKUP の4つ目の引数が「見つからないときの値」なので IFERROR で包む必要はありません。INDEX + MATCH では IFERROR を使います。
解き方のヒント
回答は E2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 対象セルは E2
- 先にキーを整える：TRIM(UPPER(E1))
- XLOOKUP の4つ目の引数が見つからないときの戻り値
- 形：=XLOOKUP(?, ?, ?, ?)
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
方法1：XLOOKUP と整えたキー・見つからない場合の値
=XLOOKUP(TRIM(UPPER(E1)), A2:A6, C2:C6, "Not found")
TRIM(UPPER(" sku-103 ")) は "SKU-103" になり A4 と一致するので、C4 の 24.99 を返します。見つからないときは4つ目の引数が "Not found" を返し、#N/A にはなりません。
方法2：INDEX + MATCH を IFERROR で包む
=IFERROR(INDEX(C2:C6, MATCH(TRIM(UPPER(E1)), A2:A6, 0)), "Not found")
整え方は同じです。MATCH が行を見つけ、INDEX が価格を返し、見つからないときは IFERROR が既定値を返します。XLOOKUP が引数で行うことを式で表しています。
関数ガイド
よくある質問
同じに見える値なのに検索が失敗するのはなぜ？
見えない空白か大文字小文字が原因です。"SKU-103" と " sku-103 " は別の文字列です。比較の前に TRIM と UPPER で整えます。
#N/A ではなく分かりやすい文言を出すには？
XLOOKUP の4つ目の引数が既定値です：=XLOOKUP(キー, キーの範囲, 値の範囲, "Not found")。INDEX + MATCH では IFERROR で包みます。