1
00:00:00,080 --> 00:00:04,261
A client has three orders, but a single-match lookup makes the

2
00:00:04,261 --> 00:00:05,655
history look like one.

3
00:00:05,655 --> 00:00:09,488
This fictional Excel sheet has five total rows and three North

4
00:00:09,488 --> 00:00:12,624
Pier orders worth two hundred twenty-five dollars together.

5
00:00:12,624 --> 00:00:16,457
We will return all three complete records with FILTER, check their

6
00:00:16,457 --> 00:00:20,290
IDs and amounts, and then test a client with no matches.

7
00:00:20,290 --> 00:00:22,032
The original table stays untouched.

8
00:00:22,632 --> 00:00:27,564
Open the fictional five-row client order list in an Excel workbook copy.

9
00:00:27,564 --> 00:00:31,737
North Pier appears on three separate orders: design, support, and consulting.

10
00:00:31,737 --> 00:00:36,289
A first-match lookup can answer which one matching record it found;

11
00:00:36,289 --> 00:00:38,565
it is not a complete history.

12
00:00:38,565 --> 00:00:42,738
The required result is three rows with all four original columns.

13
00:00:43,338 --> 00:00:45,937
Keep the source range A1:D6 intact.

14
00:00:45,937 --> 00:00:50,021
Type Client to find in F2 and North Pier in G2.

15
00:00:50,021 --> 00:00:54,847
Leave a four-column by three-row output area starting at F4 empty.

16
00:00:54,847 --> 00:00:58,931
A dynamic array formula needs room to spill; a blocking value

17
00:00:58,931 --> 00:01:02,643
can produce a spill error instead of the intended rows.

18
00:01:03,243 --> 00:01:08,222
In F4 enter =FILTER(A2:D6,B2:B6=G2,"No matching orders").

19
00:01:08,222 --> 00:01:12,786
The first argument contains all four data columns; the second tests

20
00:01:12,786 --> 00:01:16,106
Client in each corresponding source row against G2.

21
00:01:16,106 --> 00:01:21,500
Microsoft documents that FILTER returns rows meeting the condition and spills the result.

22
00:01:21,500 --> 00:01:26,064
Do not select only the Order ID column or the rest

23
00:01:26,064 --> 00:01:28,553
of each record will be lost.

24
00:01:29,153 --> 00:01:34,414
Read the three spilled IDs in order: ORD-201, ORD-203, ORD-205.

25
00:01:34,414 --> 00:01:38,461
Their services are Design, Support, and Consulting; their amounts are

26
00:01:38,461 --> 00:01:41,699
one hundred twenty, forty-five, and sixty dollars.

27
00:01:41,699 --> 00:01:46,555
Add the amounts separately to get two hundred twenty-five dollars; that

28
00:01:46,555 --> 00:01:51,007
total is an independent check, not a fourth FILTER output row.

29
00:01:51,007 --> 00:01:55,863
Confirm ORD-202 and ORD-204 are absent because their clients differ.

30
00:01:56,463 --> 00:01:58,485
Replace G2 with Missing Client.

31
00:01:58,485 --> 00:02:02,934
The optional if_empty argument makes the output say No matching orders

32
00:02:02,934 --> 00:02:06,574
instead of showing a calculation error for zero matches.

33
00:02:06,574 --> 00:02:11,427
Restore North Pier when done and confirm the same three records return.

34
00:02:11,427 --> 00:02:15,471
This checks that the query cell actually controls the result.

35
00:02:16,071 --> 00:02:20,225
If only one row appears, inspect whether you used a lookup

36
00:02:20,225 --> 00:02:23,245
instead of FILTER or accidentally shortened A2:D6.

37
00:02:23,245 --> 00:02:27,398
If you see a spill error, clear only the intended output

38
00:02:27,398 --> 00:02:29,286
area, not the source table.

39
00:02:29,286 --> 00:02:33,439
When new source orders are added after row six, expand both

40
00:02:33,439 --> 00:02:37,593
range arguments together or use a named table; the exercise formula

41
00:02:37,593 --> 00:02:39,858
does not discover extra rows automatically.

42
00:02:40,458 --> 00:02:44,594
In the fictional practice, FILTER should return ORD-201, ORD-203, and

43
00:02:44,594 --> 00:02:46,317
ORD-205 for North Pier.

44
00:02:46,317 --> 00:02:50,453
Their amounts add to two hundred twenty-five dollars, and the two

45
00:02:50,453 --> 00:02:53,210
other clients should stay out of the result.

46
00:02:53,210 --> 00:02:57,691
A no-match test should display a clear message without changing the source.

47
00:02:57,691 --> 00:03:01,827
If a row is absent, check both source-range endpoints and whether

48
00:03:01,827 --> 00:03:03,895
the output has room to spill.

49
00:03:03,895 --> 00:03:06,997
The guide has the practice CSV and independent answer.