index an array and match from array

[heljarre]

i am trying to find a formula to would allow me to search an array and find the data matches the data from another array. for instance my Array A is formatted like

EE01123

EEM02123

EE10123

and Array B is like

01

02

so i want to look at the first 2 numbers of array A and if they match options in array B then add to list. If it makes a difference i am only looking for the first 5 matches.

if it is easier i can use a macro to make this work. 

i am using EXCEL 2019 on windows.

TIA

[#2]

@heljarreHi Jarred, unfortunately you didn't tell me which Excel version you are working with. I have built a function that only works with Excel 365 or Excel for the Web, because very new functions like BYROW() are used. I hope I have understood your task correctly.

=LET(
areaA,A1:A3,
areaB,E1:E2,
maxle, MAX(LEN(areaA)),
fitwnu,BYROW(areaA,LAMBDA(in,MID(in,MIN(IFERROR((VALUE(MID(in,SEQUENCE(1,maxle),1))>=0)*SEQUENCE(1,maxle),"")),2))),
FILTER(areaA,MMULT(--(fitwnu=TRANSPOSE(areaB)),SEQUENCE(COUNTA(areaB)))>0))

[#3]

Let me start off by saying Thank You Greatly for the reply. Also I apologize for not stating the needed information upfront. i am using excel 2019 on windows.

[#4]

@heljarre 

Without dynamic arrays, your best option may be to extract the number portion of the string first with flash fill  (Type in the desired return for the first item, press enter, then press ctrl+E). Then you can run INDEX-MATCH off the cleaned up numbers:

[#5]

Ok i see what you are doing here and it works for what i am wanting to do except i need it to list the list A item not the list B option. in your example i need row E to show me the items in row C that match the criteria of row G.

i do apologize this is my first time posting for help.

[#6]

if it helps i do already have it pulling the first 2 numbers. if it would be easier to go off of those. such that column A is the pulled out numbers (01) and column b is the full ID (EEM01123), and row C has my critiria (C1=01 and C2=02) and i want row D to list the first 5 items from row B that have either 01 or 02 from row C as first 2 numbers.