index an array and match from array

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

excel

Formulas and Functions

Macros and VBA

dscheikey

Bronze Contributor

Oct 25, 2022

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))

heljarre

Copper Contributor

Oct 25, 2022

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.

Forum Discussion

index an array and match from array