Searchable Drop Down List in Data Validation without using VBA

[Lenard]

Hi

As my excel version do not have filter, sort, unique functions I have to use other set of formulas to search the drop down list in data validation.
A range of data is created with Code and BP name in column A & B under Master sheet where "Master" name range is set on column B and Data validation list is set without error alert at cell B2 and below under Entry sheet

MrExcel_Searchable DropDownList Data Validation.xlsx
	B	C
2	BP NAME	CODE
3	CD Trading Ltd	AP-C006
4	Trad
5	Co
6
7
8
9
10
Entry

Cell Formulas
Range	Formula
C3:C10	C3	=IFERROR(INDEX(Master!A:A,MATCH(Entry!B3,Master!B:B,0),1),"")

Named Ranges
Name	Refers To	Cells
Master!_FilterDatabase	=Master!$A$1:$B$1	C3:C10
Master	=OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1)	C3:C10

Cells with Data Validation
Cell	Allow	Criteria
B3:B10	List	=OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),)

MrExcel_Searchable DropDownList Data Validation.xlsx
	A	B	C
1	CODE	BP NAME
2	AP-C001	ABC Ltd
3	AP-C002	Allan Fong Bus Terminal
4	AP-C003	BCD Enterprise
5	AP-C004	CCD Co
6	AP-C005	CCD Pte Ltd
7	AP-C006	CD Trading Ltd
8	AP-C007	Coco Farm Enterprise
9	AP-C008	CoConet Wholesaler
10	AP-C009	Dast Hardware Shop
11	AP-C010	DFG Engineering Co
12	AP-C011	Farming Tree Garden
13	AP-C012	Fire Fox Construction
14	AP-C013	Flex Dasta Construction
15	AP-C014	LightDelta Distributor
16	AP-C015	Mass Com Production
17	AP-C016	Mass Global Network
18	AP-C017	PSP Distributor Co
19	AP-C018	QQ Ylang Wholesale
20	AP-C019	Rar Pile Trading Co
21	AP-C020	Wass Entertainment Show
22
Master

Below set of formulas copied from google search and modified :-
1) Master name range "OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1)"
2) Data Validation List "OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),)"

When new BP name & code are added to the list of Master sheet, the list will be updated and sorted automatically in data validation list.
If there are more than 100 names to search in drop down list, it would be more efficient to use dynamic search in drop down list.
However, when I try to do dynamic search by "Trad" or "Co" in cell B2 in Entry sheet, the drop down list show nothing ??

Did I miss out anything in my formulas ?

My objective is to make every row of data entry as dynamic search in drop down list data validation such as filtering search without using VBA and also do not use filter, sort, unique function as they work on office 365 and it will be refresh the drop down list for next row of entry.

Appreciate if anyone can help to solve the above problem and thanks in advance

Regards
Len