Forum Discussion

heljarre's avatar
heljarre
Copper Contributor
Oct 25, 2022

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

excel
Formulas and Functions
Macros and VBA
  • dscheikey's avatar
    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's avatar
      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.
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Oct 25, 2022

        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:

         

Resources