google-sheetsarray-formulas

Find the position of a cell by sub-string based on another range in an array (Google Sheets formula)

I need a specific formula that would give me the position of a cell in a column (Data!A:A) based on values form another range (A:A). Those values are strings of numbers that can be found as sub-strings of the values in Data!A:A.

I was able to successfully make a formula that does this for a single row, but I need one that would go through the whole list. What I made was:

=LET(data,Data!$A:$A,
Match(Filter(data,ISNUMBER(Search(A1,data))),data,0))

But when I try to simply wrap in an arrayformula it it gives me wrong answers

=LET(data,Data!$A:$A,
ARRAYFORMULA(IF(A:A="",,
Match(Filter(data,ISNUMBER(Search(A1:A,data))),data,0))))

I made and example sheet to visualize it - https://docs.google.com/spreadsheets/d/1-R-zWDZKarLAAYg4c5drJUolLFII0vSkt5nIuEHsWIA/edit?usp=sharing

Does anyone have an idea how to make it work?

Solution

  • You may try:

    =map(A:A,lambda(Σ,if(Σ="",,index(ifna(xmatch("*"&Σ&"*",to_text(Data!A:A),2),"-")))))

    enter image description here

    UPDATED:

    =map(A:A,lambda(Σ,if(Σ="",,index(index(split(Data!A:A,": ",),,2),xmatch("*"&Σ&"*",to_text(Data!A:A),2)+5))))

    UPDATE 2:

    = map(A:A,lambda(Σ,if(Σ="",,index(1*(REGEXEXTRACT(Data!A:A,"[\d.-]+")),xmatch("*"&Σ&"*",to_text(Data!A:A),2)+5))))