getting an approximate match (and not an exact one !!)

[shili12]

Again my sincere apologies for not uploading xlsx workbook example, but i suppose review of formula can help.

Follow up from the following thread (Solved) :-

What's causing this #NA error ? am using VSTACK and FILTER

when placing the exact details:-
formula in column B returns ??

KCB923M

??

but when omitting the last digit, the result is quite satisfactory :-
A formula is placed on B1


the formula used is =IFERROR(VSTACK(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1,'1stfebtill31stdec2021'!$E$2:$E$134137))),FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1,'1stjantill31stdec2020'!$E$2:$E$84468))),FILTER('1stjantill31stdec2022'!$A$2:$I$213575,ISNUMBER(SEARCH($A1, '1stjantill31stdec2022'!$E$2:$E$213575)))),"??")
how can i obtain exact match ?

 

[Debaser]

Why did you remove the last "??" argument from each filter function? You need that.

 

[pecoflyer]

Please post an excel sheet instead of pictures in the future. thx

 

[shili12]

thanks @Debaser ,
its working like a charm, @pecoflyer , it was hectic to upload workbook as it had full years statement for 3 years, the file is over 115MB.
i suppose by review of formula, one can point out the shortcoming in it.

KCB923M	??	??	??	??	??	??	??	??	??
	OLV25OVT2Q	31/12/2020​	31/12/2020 13:01​	Pay Bill from 254711xxxxxx - NJENGA WOKI Acc. KCB 923M	KCB923M	Completed	4155​	??	??
	OLV05NMSI4	31/12/2020​	31/12/2020 12:35​	Pay Bill from 254711xxxxxx - NJENGA WOKI Acc. KCB 923M	KCB923M	Completed	4155​	??	??
	QA372XXQRB	03/01/2022​	03/01/2022 14:41​	Pay Bill from 254711xxxxxx - NJENGA WOKI Acc. KCB 923M	KCB923M	Completed	4155​	0​	4155​

the formula is corrected as you indicated by placing it in FILTER "??", the very last ?? pertains to IFERROR:-
=IFERROR(VSTACK(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1,'1stfebtill31stdec2021'!$E$2:$E$134137)),"??"),FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1,'1stjantill31stdec2020'!$E$2:$E$84468)),"??"),FILTER('1stjantill31stdec2022'!$A$2:$I$213575,ISNUMBER(SEARCH($A1, '1stjantill31stdec2022'!$E$2:$E$213575)),"??")),"??")