1 00:00:00,080 --> 00:00:03,178 Six orders need unit prices from a separate catalog. 2 00:00:03,178 --> 00:00:06,964 Five SKUs match, but one was typed as N O dash 3 00:00:06,964 --> 00:00:09,030 nine nine nine and is absent. 4 00:00:09,030 --> 00:00:12,816 We will use Excel XLOOKUP to return a price for valid 5 00:00:12,816 --> 00:00:16,258 keys and a visible Missing SKU label for the exception. 6 00:00:16,258 --> 00:00:20,045 At the end, the five matched orders total two hundred seventy 7 00:00:20,045 --> 00:00:24,175 six dollars and fifty cents; the missing row stays open for review. 8 00:00:24,775 --> 00:00:26,427 Download the practice workbook. 9 00:00:26,427 --> 00:00:30,971 Orders contains order ID, SKU, quantity, and two empty output columns. 10 00:00:30,971 --> 00:00:35,927 Catalog contains five unique SKU keys, product names, and numeric unit prices. 11 00:00:35,927 --> 00:00:39,644 The invented missing SKU is in Orders row five. 12 00:00:39,644 --> 00:00:44,187 Keep the source catalog intact while you build formulas in the 13 00:00:44,187 --> 00:00:48,730 yellow Orders cells; this makes a later correction easy to audit. 14 00:00:49,330 --> 00:00:53,291 In Orders, cell B two is the first SKU to find. 15 00:00:53,291 --> 00:00:57,252 On Catalog, cells A two through A six are the lookup 16 00:00:57,252 --> 00:01:01,213 keys, and C two through C six are the matching prices. 17 00:01:01,213 --> 00:01:02,653 Those ranges must align. 18 00:01:02,653 --> 00:01:06,614 Microsoft's XLOOKUP documentation says its default match is exact, which 19 00:01:06,614 --> 00:01:08,054 is appropriate for identifiers. 20 00:01:08,054 --> 00:01:13,095 We will anchor both catalog ranges so they do not move when copied down. 21 00:01:13,695 --> 00:01:17,800 In Orders D two, enter the XLOOKUP formula printed in the guide. 22 00:01:17,800 --> 00:01:21,563 It searches B two in the fixed Catalog SKU range, returns 23 00:01:21,563 --> 00:01:25,326 the price, and uses Missing SKU as the fourth argument when 24 00:01:25,326 --> 00:01:26,695 no key is found. 25 00:01:26,695 --> 00:01:31,484 D two should show twenty four dollars for B K dash one zero one. 26 00:01:31,484 --> 00:01:35,247 If Excel uses a semicolon separator in your locale, follow its 27 00:01:35,247 --> 00:01:37,641 formula prompt while keeping the same arguments. 28 00:01:38,241 --> 00:01:40,498 Copy D two through D seven. 29 00:01:40,498 --> 00:01:44,259 Matched prices should be twenty-four dollars, eighteen dollars, twelve 30 00:01:44,259 --> 00:01:48,396 dollars and fifty cents, then Missing SKU in D five, followed 31 00:01:48,396 --> 00:01:52,533 by twenty-seven dollars and fifty cents and thirty-nine dollars. 32 00:01:52,533 --> 00:01:54,038 Stop at the exception. 33 00:01:54,038 --> 00:01:58,551 Check the order key against the catalog; do not invent a price. 34 00:01:58,551 --> 00:02:02,688 Resolve duplicate catalog keys too, because XLOOKUP returns the first match. 35 00:02:03,288 --> 00:02:07,565 In E two, use the guide's IF formula: when D two 36 00:02:07,565 --> 00:02:11,841 says Missing SKU, show Review SKU; otherwise multiply quantity by unit price. 37 00:02:11,841 --> 00:02:13,980 Fill E two through E seven. 38 00:02:13,980 --> 00:02:17,187 The missing row stays text, not a false zero. 39 00:02:17,187 --> 00:02:21,107 The five line totals are forty-eight dollars, eighteen dollars, fifty 40 00:02:21,107 --> 00:02:25,028 dollars, eighty-two dollars and fifty cents, and seventy-eight dollars, 41 00:02:25,028 --> 00:02:27,166 matching the workbook's row order. 42 00:02:27,766 --> 00:02:30,657 Compare each row with the independent answer check. 43 00:02:30,657 --> 00:02:34,632 Five orders have numeric totals, one says Review SKU, and the 44 00:02:34,632 --> 00:02:39,692 sum of the five numbers is two hundred seventy six dollars and fifty cents. 45 00:02:39,692 --> 00:02:43,667 If you see a different total, inspect the catalog range anchors 46 00:02:43,667 --> 00:02:46,920 and the order quantities before changing any source values. 47 00:02:46,920 --> 00:02:51,979 The guide includes the exact formulas and a checklist for version or locale differences.