Forum Discussion
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...
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))
- 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.
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:
- 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.
