Ravindra BagaleCourses & study guides मराठी Track your progress

Excel FILTER Function: Return All Matching Rows, Multiple Conditions, Starts With and #SPILL! Error

By Last updated:

Every lookup returns one answer. Ask XLOOKUP for Salman and you get either the first Salman or the last, never both. FILTER returns every row that matches, as a live list that updates when the data changes. In this chapter you will filter by one condition, by two (AND and OR), show a message when nothing matches, find names that start with or contain some letters, and fix the #SPILL! error.

What you'll learn in this chapter

  • FILTER syntax: array, include, if_empty
  • Every match instead of one
  • Return one column or full rows
  • Numbers: total of 200 or more
  • AND with *, OR with +
  • A message when nothing matches (and #CALC!)
  • Starts with (LEFT) and contains (SEARCH)
  • The #SPILL! error and how to fix it

Practice file: excel-filter-function-practice.xlsx: ten FILTER examples on one sheet, each one formula in its top-left cell, and a Your turn sheet.

Which Excel has FILTER? Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In older versions, use the Filter button (Data › Filter) to see matching rows.

1. The table

The student table from the INDEX and MATCH chapter: roll (A), name (B), maths (C), science (D), english (E), total (F), result (G), percentage (H), data in A5:H15. Two students are called Salman: roll 104 (Pass) and roll 111 (Fail).

2. FILTER syntax

=FILTER(array, include, [if_empty])

  1. array: what to return: one column, or the whole table.
  2. include: the condition, written as a test on a column of the same height, such as B5:B15="Salman".
  3. if_empty (optional): what to show if no row matches.

3. Every match, not just one

=FILTER(G5:G15,B5:B15="Salman")

→ Pass and Fail, one below the other. Both Salmans, at last. But which result belongs to which Salman? Return the whole rows instead:

=FILTER(A5:H15,B5:B15="Salman")

→ two full rows: 104 Salman 88 35 72 195 Pass 65.00 and 111 Salman 30 55 41 126 Fail 42.00. You typed one formula in one cell; the answer spills into as many rows and columns as it needs.

XLOOKUP gives one match; FILTER gives all of them =XLOOKUP("Salman",$B$5:$B$15,$F$5:$F$15) → 195 only. The second Salman is never seen. =FILTER(A5:H15,B5:B15="Salman") typed once in J5, spills 2 rows × 8 columns J K L M N O P Q 5 104 Salman 88 35 72 195 Pass 65.00 6 111 Salman 30 55 41 126 Fail 42.00 Change a name in column B to Salman and a third row appears at once. FILTER: Excel 2021, 2024, Microsoft 365 and Excel for the web.

Figure 1. XLOOKUP returns one Salman. FILTER returns every row where the name is Salman, full rows included.

Classroom line

Seeing this, you may wonder: if it is this easy, why did we spend time learning all those lookups?

Classroom line

At work we use FILTER more. But lookups are asked in interviews, so you need to learn them too.

4. Conditions on numbers

Students with a total of 200 or more:

=FILTER(A5:H15,F5:F15>=200)

→ four rows: Ravindra 229, Shraddha 270, Zoya 241, Rani 246. Change Zoya's maths to 40 and her total drops to 214; she stays. Change it to 20 and she disappears from the list at once.

Only the names of students who failed:

=FILTER(B5:B15,G5:G15="Fail")

→ Ruhi, Shahrukh, Ravina, Salman.

5. Two conditions: AND and OR

The obvious idea, AND(C5:C15>=80,F5:F15>=200), does not work. AND looks at all 22 values and returns a single TRUE or FALSE for the whole lot, not one answer per row, and FILTER shows #VALUE!. Joining the tests with & doesn't work either. Nor does a comma between them: FILTER's third argument is if_empty, so a second condition after a comma is silently ignored, and a third one gives a "too many arguments" message.

AND: multiply the conditions.

=FILTER(A5:H15,(C5:C15>=80)*(F5:F15>=200))

Each test gives a column of TRUE/FALSE values. Multiplying turns TRUE into 1 and FALSE into 0, so a row gives 1 only if both tests are TRUE (1 × 1). Result: Shraddha (92, 270) and Rani (81, 246). Salman (104) has 88 in maths but a total of 195, so 1 × 0 = 0 and he is left out.

OR: add the conditions.

=FILTER(A5:H15,(C5:C15>=85)+(E5:E15>=85))

A row passes if the sum is 1 or more, so either test is enough: Shraddha (92 maths, 90 english), Salman 104 (88 maths) and Rani (89 english). Shraddha matches both tests (sum 2) and still appears only once.

Each condition needs its own brackets: (C5:C15>=80)*(F5:F15>=200).

Multiply for AND, add for OR name C>=80 F>=200 AND × C>=85 E>=85 OR + Ravindra 0 1 0 0 0 0 Shraddha 1 1 1 1 1 2 Ruhi 0 0 0 0 0 0 Salman 104 1 0 0 1 0 1 Shahrukh 0 0 0 0 0 0 Zoya 0 1 0 0 0 0 Amir 0 0 0 0 0 0 Ravina 0 0 0 0 0 0 Raja 0 0 0 0 0 0 Rani 1 1 1 0 1 1 Salman 111 0 0 0 0 0 0 AND (C5:C15>=80)*(F5:F15>=200) → Shraddha, Rani both tests must be 1 OR (C5:C15>=85)+(E5:E15>=85) → Shraddha, Salman 104, Rani one test is enough TRUE counts as 1, FALSE as 0. FILTER keeps every row whose number is not 0.

Figure 2. Each test gives TRUE (1) or FALSE (0) per row. Multiply for AND: 1 only if both are 1. Add for OR: 1 or more if either is 1.

Ravindra Bagale's Tip

Before you trust a FILTER with two conditions, type each condition alone in a spare column, for example =(C5:C15>=80)*(F5:F15>=200). You see the 1s and 0s row by row and can check them against the data. Once the 1s are on the right rows, put the condition into FILTER.

6. When nothing matches

Maths 95 or more and total 200 or more: nobody qualifies. Without the third argument, FILTER shows #CALC! (the result is empty). Add a message:

=FILTER(A5:H15,(C5:C15>=95)*(F5:F15>=200),"No student found")

→ No student found. Always add if_empty to a FILTER that someone else will use; a message is clearer than an error.

When nothing matches: if_empty =FILTER(A5:H15,(C5:C15>=95)*(F5:F15>=200)) → #CALC! (an empty result) =FILTER(A5:H15,(C5:C15>=95)*(F5:F15>=200),"No student found") → No student found The third argument is only for this message. A second condition goes inside include with * or +, not after a comma.

Figure 3. No row passes both tests. Without if_empty FILTER shows #CALC!; with it, your message.

7. Names that start with some letters

=FILTER(B5:B15,B5:B15="ra*") finds nothing. Inside a condition, = compares exactly, so * is just a star, not a wildcard. (XLOOKUP's match_mode 2 understands wildcards, but it still returns only the first match.)

Use LEFT, which takes the first characters of a text: =LEFT("Ravindra",2) → "Ra".

=FILTER(B5:B15,LEFT(B5:B15,2)="ra")

→ Ravindra, Ravina, Raja, Rani. The comparison ignores capital letters, so "Ra" matches "ra".

Contains, anywhere in the name: SEARCH returns the position of the text, or an error if it is not there. ISNUMBER turns that into TRUE/FALSE:

=FILTER(B5:B15,ISNUMBER(SEARCH("ra",B5:B15)))

→ Ravindra, Shraddha, Ravina, Raja, Rani. Shraddha is new: "ra" sits inside her name (Shraddha).

Starts with (LEFT) vs contains (SEARCH) name LEFT(…,2) ="ra"? SEARCH("ra",…) Ravindra Ra TRUE 1 Shraddha Sh FALSE 3 Ruhi Ru FALSE #VALUE! Salman Sa FALSE #VALUE! Shahrukh Sh FALSE #VALUE! Zoya Zo FALSE #VALUE! Amir Am FALSE #VALUE! Ravina Ra TRUE 1 Raja Ra TRUE 1 Rani Ra TRUE 1 Salman Sa FALSE #VALUE! =FILTER(B5:B15,LEFT(B5:B15,2)="ra") → Ravindra, Ravina, Raja, Rani starts with "ra" (capitals ignored) =FILTER(B5:B15,ISNUMBER(SEARCH("ra",B5:B15))) → Ravindra, Shraddha, Ravina, Raja, Rani contains "ra" anywhere

Figure 4. LEFT(name, 2)="ra" keeps names that start with ra. SEARCH finds ra anywhere, so Shraddha joins the list.

8. The #SPILL! error

A FILTER result needs empty cells to spill into. If anything is in the way, even a single number or a space, the formula shows #SPILL! and no results.

Try it:

  1. Write =FILTER(A5:H15,F5:F15>=200) in J5. It spills four rows into J5:Q8.
  2. Type 9 in L7. J5 shows #SPILL!.
  3. Click J5: Excel draws a dashed border around the range it needs.
  4. Delete L7. The four rows come back.

SPILL! also appears if the spill range would run off the sheet or into merged cells. The fix is always the same: give it room.

#SPILL!: something is in the way =FILTER(A5:H15,F5:F15>=200) in J5 J K L M 5 101 Ravindra 78 85 6 102 Shraddha 92 88 7 106 Zoya 67 90 8 110 Rani 81 76 4 rows spill (to column Q) J K L M 5 #SPILL! 6 7 9 8 9 in L7 → #SPILL!. Delete it → the rows return. Also caused by merged cells or a range that would run off the sheet. Fix: give it room.

Figure 5. A 9 typed inside the spill range blocks the result. Delete it and the four rows spill back.

9. The whole chapter on one page

  1. =FILTER(array, include, [if_empty]) returns every matching row.
  2. Return one column (G5:G15) or the whole table (A5:H15).
  3. AND = multiply conditions; OR = add them; each in its own brackets.
  4. The AND() function, & or a comma between conditions don't work inside FILTER.
  5. No match → #CALC!; the third argument shows your message instead.
  6. = is exact: use LEFT(...)="ra" for starts with and ISNUMBER(SEARCH("ra",...)) for contains.

  7. SPILL! = something is in the way; clear the cells.

  8. FILTER: Excel 2021, 2024, Microsoft 365 and Excel for the web.

10. Try at home

Try at home

Task 1

  1. List every student whose result is Pass and whose english is 80 or more. (Shraddha, Zoya, Rani.)

Task 2

  1. List names that start with "sh". (Shraddha, Shahrukh.)

Task 3

  1. List students with maths below 35 or science below 35. (Ruhi, Ravina, Salman 111.)

Task 4

  1. Write a FILTER that finds nobody and add the message "No student found".

Recap

In short

  1. Lookups return one match; FILTER returns all of them.
  2. * between conditions = AND, + = OR.
  3. Add if_empty so nobody sees #CALC!.
  4. Use LEFT for starts with and SEARCH for contains.

  5. SPILL! means the result has no room.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: FILTER with conditions. Example data table with All Pune rows: Amir and Shahrukh; Pune OR Nashik: Amir, Zoya, Shahrukh; Amounts over ₹1,000: None.
Card 1 of 2: FILTER with conditions · Download PNG
Revision card: FILTER errors. Covers #SPILL!; #CALC!; #VALUE!. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: FILTER errors · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. VLOOKUP name (Easy): Name for ID "O103" with VLOOKUP. (sheet "Lookup")
  2. XLOOKUP value (Easy): The Unit price for ID "O103". (sheet "Lookup")
  3. Position (Easy): Position of "Emma Schmidt" in B2:B9. (sheet "Lookup")
  4. Distinct groups (Easy): How many different values in B2:B11? (sheet "Arrays")
  5. List groups (Easy): Unique list of B2:B11. (sheet "Arrays")
  6. Filter names (Easy): Names in A2:A11 where B is "Website". (sheet "Arrays")
  7. INDEX and MATCH (Medium): ID for name "Emma Schmidt" using INDEX and MATCH. (sheet "Lookup")
  8. Not found message (Medium): Quantity for ID "Z999", or "Not found". (sheet "Lookup")
  9. IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing". (sheet "Lookup")
  10. Sum of filtered (Medium): Sum of D2:D11 where B is "App", using FILTER. (sheet "Arrays")
  11. Count filtered (Medium): Rows where D is above the average of D2:D11. (sheet "Arrays")
  12. Sorted values (Medium): D2:D11 sorted largest first. (sheet "Arrays")

See all practice tasks in the Task Hub →