# Filter Function error

Occasional Contributor

# Filter Function error

Hello, Could some please help with the issue below? I using filter function to find the invoice # in column B, it is working but not in the last result where I have and error.

 A B C D E 1 Code CLÉ Vendor invoice Number 2 E.BRITO - 2018-05 - ENG.43A00 - 5 =FILTRE(\$E\$2:\$E\$6;\$D\$2:\$D\$6=A2) E.BRITO - 2018-05 - ENG.43A00 - 5 38301175 3 E.BRITO - 2018-06 - ENG.33000 - 3,5 =FILTRE(\$E\$2:\$E\$6;\$D\$2:\$D\$6=A3) E.BRITO - 2018-06 - ENG.33000 - 3,5 38301175 4 E.BRITO - 2018-06 - ENG.33000 - 3,5 =FILTRE(\$E\$2:\$E\$6;\$D\$2:\$D\$6=A4) E.BRITO - 2018-06 - ENG.43A00 - 3,5 38301175 5 E.BRITO - 2018-06 - ENG.33000 - -3,5 =FILTRE(\$E\$2:\$E\$6;\$D\$2:\$D\$6=A5) E.BRITO - 2018-06 - ENG.33000 - -3,5 38338367 6 E.BRITO - 2018-06 - ENG.43A00 - 3,5 =FILTRE(\$E\$2:\$E\$6;\$D\$2:\$D\$6=A6) E.BRITO - 2018-06 - ENG.43A00 - 3,5 38338367

8 Replies

# Re: Filter Function error

Hi @Eliecer

It's highly probable that values in A6 and D6 don't compare (i.e. extra space(s) in one of them). To check, in an empty cell enter =D6=A6

If you get FALSE this confirms the values don't compare. If you get TRUE then please share your sample workbook & explain where the data in D2:E6 come from (i.e. copy/paste from a Web page)

# Re: Filter Function error

``=FILTRE(\$E\$2:\$E\$6;\$D\$2:\$D\$6=A6)``

#EPARS! seems to be the #SPILL! error. You can try to delete the #CALC! error in cell B7 and the formula should return the expected result.

# Re: Filter Function error

Hi L z., I compared and the result is true,. The data is coming from a huge Excel data base and I just extracted a sample of the issue. I attached the file for testing.
Thank you!

# Re: Filter Function error

Hi Quadruple_Pawn, #EPARS! is in french for #SPILL! error. You are right when I deleted #CALC! error in cell B7 I have the correct result but not in B6 as expected but in B7.

# Re: Filter Function error

Hi Harun24HR, I can attach an Excel spreadhseet so In inserted a table with data.

Thank you!

# Re: Filter Function error

Does this mean you expect 38338367 as the only result for E.BRITO - 2018-06 - ENG.43A00 - 3,5?

E.BRITO - 2018-06 - ENG.43A00 - 3,5 is in cells D4 and D6 with different values in cells E4 and E6 and therefore has to return both invoice numbers according to my expectation.

Is there any additional criteria if you only want to return 38338367?

# Re: Filter Function error

I expected to have have as result 38338367 in B6 since one of the line in B3 and B4 was credited an reinvoinced as shown in E5 and E6, the credited is right but not the reinvoiced line, it is returning 38301175 instead of 38338367 in B6