1
00:00:00,080 --> 00:00:03,838
The fictional product list contains P-106 at three dollars and

2
00:00:03,838 --> 00:00:06,914
twenty-five cents, but it has no P-107.

3
00:00:06,914 --> 00:00:11,355
An approximate VLOOKUP may return a neighboring row's price for P-107

4
00:00:11,355 --> 00:00:14,089
and make it look like a valid item.

5
00:00:14,089 --> 00:00:17,847
Exact match should instead show N A for the missing code,

6
00:00:17,847 --> 00:00:21,264
while a real code must still return its true price.

7
00:00:21,864 --> 00:00:25,501
Open the original practice workbook and inspect the five fictional

8
00:00:25,501 --> 00:00:27,684
product rows in A1 through C6.

9
00:00:27,684 --> 00:00:31,685
Product Code is the first column of the VLOOKUP range, and

10
00:00:31,685 --> 00:00:33,139
Price is column three.

11
00:00:33,139 --> 00:00:37,868
The lookup code P-107 and an approximate-match formula are prefilled so

12
00:00:37,868 --> 00:00:40,414
you can see the wrong nearby price.

13
00:00:40,414 --> 00:00:45,506
P-104 is Binder at eight dollars and seventy-five cents; P-107 is

14
00:00:45,506 --> 00:00:47,325
absent from the source table.

15
00:00:47,925 --> 00:00:53,378
The function is VLOOKUP of lookup value, table array, return column and range lookup.

16
00:00:53,378 --> 00:00:58,441
Microsoft says the optional fourth argument defaults to TRUE, which means approximate match.

17
00:00:58,441 --> 00:01:03,895
If it is missing, a plausible-looking price may come from a different code.

18
00:01:04,495 --> 00:01:08,051
For this lookup table, use FALSE as the fourth argument.

19
00:01:08,051 --> 00:01:12,673
Keep the source range fixed so copying the formula does not move it.

20
00:01:12,673 --> 00:01:17,651
With P-107 in E2, the exact-match result should be #N/A because

21
00:01:17,651 --> 00:01:19,429
there is no such code.

22
00:01:20,029 --> 00:01:22,021
Change E2 to P-104.

23
00:01:22,021 --> 00:01:26,401
The exact formula should return eight dollars and seventy-five cents,

24
00:01:26,401 --> 00:01:27,994
matching the Binder row.

25
00:01:27,994 --> 00:01:31,977
Compare the result with column C in the source table.

26
00:01:31,977 --> 00:01:36,358
This positive check distinguishes a working lookup from a formula that

27
00:01:36,358 --> 00:01:37,951
always returns an error.

28
00:01:38,551 --> 00:01:40,844
Change E2 back to P-107.

29
00:01:40,844 --> 00:01:45,812
A #N/A result is useful: there is no exact code to return.

30
00:01:45,812 --> 00:01:49,634
Do not hide it with a made-up zero price.

31
00:01:49,634 --> 00:01:54,221
If a supposedly valid code also returns #N/A, inspect spaces, text

32
00:01:54,221 --> 00:01:58,807
formatting and the first column before treating it as a VLOOKUP bug.

33
00:01:59,407 --> 00:02:02,275
FALSE fixes the accidental approximate-match branch.

34
00:02:02,275 --> 00:02:08,419
A wrong return-column number or a duplicate code can also return an unwanted value.

35
00:02:08,419 --> 00:02:12,925
Check column three for Price and ensure the fictional code list

36
00:02:12,925 --> 00:02:16,612
is unique before generalizing this result to other workbooks.

37
00:02:17,212 --> 00:02:22,135
The practice answer has two required cells: P-104 gives eight dollars

38
00:02:22,135 --> 00:02:26,238
and seventy-five cents, and P-107 gives N A.

39
00:02:26,238 --> 00:02:29,521
The source table itself is the independent reference.

40
00:02:29,521 --> 00:02:34,444
A nearby price for P-107 is the failure we are correcting.