Filter function does not seem to work

Jesus Torralba 40 Reputation points
2026-11-10T01:30:34.1966667+00:00

I need to filter only Jane what is the syntax for filter function I can't seem to make it work, I've searched exceljet

Microsoft 365 and Office | Excel | For home | Windows
0 comments
Answer accepted by question author

AI answer

2026-11-10T01:31:01.09+00:00

To return only rows where the Name column contains exactly Jane, enter =FILTER(A2:D100,B2:B100="Jane","No match") in an empty cell.

Replace:

  • A2:D100 with the complete range you want returned.
  • B2:B100 with the Name column.
  • "Jane" with a cell reference, such as F1, if you prefer to type the name into a separate cell.

For example:

=FILTER(A2:D100,B2:B100=F1,"No match")

Type Jane in F1. Excel will return every matching row and spill the results into the cells below and beside the formula. The return range and condition range must cover the same number of rows. The third argument displays No match instead of #CALC! when Jane is not found.^1^

If you want to return only the names rather than complete rows:

=FILTER(B2:B100,B2:B100="Jane","No match")

If the formula produces #SPILL!, clear any populated or merged cells in the area where the results need to appear. Also ensure that quotation marks surround literal text such as "Jane".

References

  1. FILTER function | Microsoft Support
AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jesus Torralba 40 Reputation points
    2026-11-10T07:36:32.77+00:00

    Something was wrong with my worksheet setup, it works now that I created a new worksheet. Thank you

    Was this answer helpful?

    0 comments
  2. Jesus Torralba 40 Reputation points
    2026-11-10T01:46:04.84+00:00

    I should be able to reference or put a "jane" or "Jane" all i get is a #REF, do i need to share the worksheet?

    Was this answer helpful?

    0 comments
  3. Jesus Torralba 40 Reputation points
    2026-11-10T01:42:03.04+00:00

    All setting to Microsoft office have never been changed

    Was this answer helpful?

    0 comments
  4. Jesus Torralba 40 Reputation points
    2026-11-10T01:38:00.88+00:00

    I get #REF

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.