1 00:00:00,080 --> 00:00:04,887 Your lookup SKU appears in the product table, yet VLOOKUP returns number not available. 2 00:00:04,887 --> 00:00:08,663 In the fictional file, the request cell has one trailing space 3 00:00:08,663 --> 00:00:10,380 that is hard to see. 4 00:00:10,380 --> 00:00:14,156 We will prove the mismatch with a length check, remove only 5 00:00:14,156 --> 00:00:17,246 the unwanted space, and return the eight-dollar price. 6 00:00:17,246 --> 00:00:21,023 We will keep exact matching on, so the formula cannot quietly 7 00:00:21,023 --> 00:00:22,396 choose a nearby item. 8 00:00:22,996 --> 00:00:27,163 The practice workbook lists three products: PR one-oh-one costs twelve 9 00:00:27,163 --> 00:00:32,372 dollars, PR one-oh-two costs eight, and PR one-oh-three costs twenty-five. 10 00:00:32,372 --> 00:00:36,539 To make the hidden-space error repeatable, enter the formula shown on 11 00:00:36,539 --> 00:00:41,748 screen in E two: quoted PR dash one-oh-two joined to character thirty-two. 12 00:00:41,748 --> 00:00:45,567 Google Sheets may trim a space typed directly into a cell, 13 00:00:45,567 --> 00:00:48,693 so this formula generates one ordinary trailing space reliably. 14 00:00:49,293 --> 00:00:53,321 In an empty result cell, enter VLOOKUP with E two, absolute 15 00:00:53,321 --> 00:00:56,983 range A two through B four, index two, and FALSE. 16 00:00:56,983 --> 00:01:00,646 The exact formula is on screen and in the guide. 17 00:01:00,646 --> 00:01:05,040 FALSE is important because omitting the last argument can use approximate matching. 18 00:01:05,040 --> 00:01:09,069 The expected first result is number not available; that is useful 19 00:01:09,069 --> 00:01:12,731 evidence that the visually similar key is not actually equal. 20 00:01:13,331 --> 00:01:16,752 In another empty cell, enter LEN of E two. 21 00:01:16,752 --> 00:01:21,694 It returns seven, although PR one-oh-two has only six visible characters. 22 00:01:21,694 --> 00:01:25,495 Then enter LEN of TRIM E two. That returns six. 23 00:01:25,495 --> 00:01:29,677 This pair proves one removable ordinary space exists in the request. 24 00:01:29,677 --> 00:01:33,858 Do not assume every VLOOKUP error has this cause; a wrong 25 00:01:33,858 --> 00:01:38,419 range, missing SKU, or number stored as text needs a different diagnosis. 26 00:01:39,019 --> 00:01:42,981 Wrap E two in TRIM inside the VLOOKUP search key: equals 27 00:01:42,981 --> 00:01:46,943 VLOOKUP, open parenthesis, TRIM of E two, comma, the same absolute 28 00:01:46,943 --> 00:01:50,185 two-column range, comma two, comma FALSE, close parenthesis. 29 00:01:50,185 --> 00:01:54,867 The clean key now matches PR one-oh-two in the first column, 30 00:01:54,867 --> 00:01:57,028 so the returned price is eight. 31 00:01:57,028 --> 00:02:00,630 The source product table is not changed by this formula. 32 00:02:01,230 --> 00:02:05,287 IFNA can replace the error with a friendly message, but it 33 00:02:05,287 --> 00:02:10,451 does not tell you whether a key is wrong or a product is missing. 34 00:02:10,451 --> 00:02:14,140 First inspect the range, exact-match argument, and key length. 35 00:02:14,140 --> 00:02:17,828 TRIM handles ordinary spaces but not every invisible Unicode character. 36 00:02:17,828 --> 00:02:21,886 If the corrected lookup still fails, inspect the exact characters and 37 00:02:21,886 --> 00:02:26,681 whether the SKU is text in both locations before changing the business result. 38 00:02:27,281 --> 00:02:31,040 The final answer is not just the absence of an error. 39 00:02:31,040 --> 00:02:35,484 PR one-oh-two must map to eight dollars, while the other product 40 00:02:35,484 --> 00:02:37,534 prices remain twelve and twenty-five. 41 00:02:37,534 --> 00:02:40,952 The length test explains why the original exact match failed. 42 00:02:40,952 --> 00:02:44,712 Compare your formulas and results with the answer file; if you 43 00:02:44,712 --> 00:02:48,472 get a different product, check the first column and the index 44 00:02:48,472 --> 00:02:50,523 rather than accepting an approximate match.