Filter Function error

Occasional Contributor

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.

 Thank you in advance for your helps

 

Eliecer_2-1659225861548.png

 

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

 

8 Replies
Attach a sample file so that we can copy/download and test your formula.

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)

@Eliecer 

=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.

filter.JPG 

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!
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.
Hi Harun24HR, I can attach an Excel spreadhseet so In inserted a table with data.

Thank you!

@Eliecer 

filtre.JPG

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?

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