Share via

textjoin and filter returning #N/A

Sallin, Joshua 120 Reputation points
2025-11-12T23:56:41.1566667+00:00

Hi - I'm trying to use textjoin and filter together and I keep getting #N/A. All of the columns that I am trying to filter/textjoin are results of formulas.

When I cut/paste the files into another spreadsheet and paste as values (vs formulas), the textjoin/filter formula works fine.

When I cut/paste the value that I am trying to match into a cell as a value (D4), and then paste the value that I am comparing it ('samples_10Nov2025'!J:J) into another cell as a value, they match (conditional formatting) so I'm not sure what I am doing wrong. I've done this same formula many times with no issues but doesn't matter what I do, I get #N/A.

Any thoughts? I realize it's hard to provide advice without sharing the spreadsheet but I can't share it.

Here is formula in case you see any issues with it.

=TEXTJOIN(", ",TRUE,FILTER('Samples_10Nov2025'!B:B,'Samples_10Nov2025'!J:J=D4))

Thanks!!!!

Microsoft 365 and Office | Excel | For business | Windows
0 comments

Answer accepted by question author

Jeanie H 11,915 Reputation points Microsoft External Staff Moderator
2025-11-13T01:59:47.0066667+00:00

Dear @Sallin, Joshua, 

Thank you for posting your question in the Microsoft Q&A forum.  

Based on my understanding, you are using a filter formula combined with a text join and getting the #N/A error (Not Available). Formula: ...FILTER(Column B, Column J = Cell D4). 

Column B and Column J are both results of other formulas (not hand-typed data). 

When you paste the data as values (ie only get the final result, remove all formulas), the TEXTJOIN/FILTER formula works fine. This proves that the formula is not faulty because of incorrect filtering logic, so I am wondering if the error is due to a data type mismatch? 

Excel stores data as Text or Number. For example, the number "12345" can be stored as Number (used for calculation) or Text (just a string of characters, not calculated). For example, although Cell D4 and Column J both appear to the naked eye as "ABC-101". 

The formula that creates Cell D4 may return the Text "ABC-101". 

The formula that creates Column J returns the Number 101 (due to a rounding or conversion formula). 

So the FILTER function will compare: Is the Text "ABC-101" equal to the Number 101? The answer is no, so it finds no result and returns the #N/A error. 

In the meantime, if my guess is correct (since I can only imagine, I don't have a file to test it on), you need to check Column J and Cell D4. Column B is the data column you want to return, not the column participating in the comparison logic, so it is not the cause of the #N/A error.

=FILTER requires the data type of the elements it compares ('Samples_10Nov2025'!J:J and D4) to be exactly the same.

In an empty cell, try checking the data type of the main components:

For the lookup value in D4: =ISTEXT(D4).

For a cell in the lookup column (for example, J2): =ISTEXT('Samples_10Nov2025'!J2)

If one returns true and the other returns false, then you have a mismatch error.

If there’s anything I may have misunderstood, I would truly appreciate it if you could provide a bit more detail to help me better understand your needs. 

Looking forward to your response! 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Was this answer helpful?

1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sallin, Joshua 120 Reputation points
    2025-11-13T03:22:50.9966667+00:00

    Hi Jeanie - happy to accept it and feel free to summarize. Thanks!

    Was this answer helpful?

  2. Sallin, Joshua 120 Reputation points
    2025-11-13T02:52:15.52+00:00

    hi - I tried to open and repair and it found some issues, but still not working. I re-set up all my concatenations hoping maybe that would help but it didn't. I believe it has to do with the "filter" function as I just changed the formula to an xlookup and it was fine - it found a match so the two values are finding each other - just not working with filter apparently. I'll just copy it over to the new spreadsheet that is working. Thanks for your help!!

    Was this answer helpful?

  3. Sallin, Joshua 120 Reputation points
    2025-11-13T02:11:48.79+00:00

    Thanks for your help. I did the "istext" for both and they both came "True". It's really weird as I created a new spreadsheet with the same information, same formulas, and everything works fine. So wondering if my current spreadsheet is just corrupt. I appreciate your help and thorough answer.

    Was this answer helpful?

  4. AI answer

    2025-11-12T23:57:04.3266667+00:00

    The issue you are experiencing with the TEXTJOIN and FILTER functions returning #N/A could be due to several factors related to the data types and the way Excel handles formulas and values. Here are some potential reasons and solutions:

    1. Formula Results: Since the columns you are filtering are results of formulas, ensure that these formulas are returning values that are compatible with the comparison you are making. Sometimes, formulas may return errors or unexpected results that lead to #N/A when used in a filter.
    2. Data Types: Ensure that the data types of the values you are comparing are the same. For instance, if D4 is a text value and the values in 'Samples_10Nov2025'!J:J are numbers or dates, the comparison will fail, resulting in #N/A. You can use the TEXT function to convert numbers or dates to text if needed.
    3. Blank Cells: If there are any blank cells in the range you are filtering, they might also cause the FILTER function to return #N/A. You can modify your FILTER function to exclude blanks by adding a condition to check for non-blank values.
    4. Array Size: Ensure that the ranges you are using in TEXTJOIN and FILTER are of the same size. If they are not, it may lead to errors in the filtering process.
    5. Use of IFERROR: You can wrap your FILTER function with IFERROR to handle any errors gracefully. For example: 
         =TEXTJOIN(", ", TRUE, IFERROR(FILTER('Samples_10Nov2025'!B:B, 'Samples_10Nov2025'!J:J=D4), ""))
      This will return an empty string instead of #N/A if the filter fails.

    By checking these aspects, you should be able to identify the cause of the #N/A error and resolve it. If the issue persists, consider simplifying your formula or breaking it down into smaller parts to troubleshoot more effectively.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    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.